PHP, database Get and Post

Question

I am in the process of building a careers part of a site and have written this PHP to talk to an app that I have not built yet. The problem is that when json_encode it only displays the first row of my database. Any pointers in the right direction would be much appreciated. Thanks, Jordan

<?php

error_reporting(E_ALL);

ini_set('display_errors', '1');

//variables
$server = 'localhost';
$user = 'root';
$password = '';
$db = 'starkdb';

// Connect to Database   
$connection = mysql_connect($server, $user, $password)
    or die ("Could not connect to server ... \n" . mysql_error ());

mysql_select_db($db)
    or die ("Could not connect to database ... \n" . mysql_error ());



//Check if the skill has been updated. If it has, process and save it to the database
//POST update
if (isset($_POST['update']))
{
    //Confirm that the 'id' value is a valid integer before getting the data
    if (is_numeric($_POST['id']))

    {
        //Retrieve the data
        $id = $_POST['id'];
        $title = mysql_real_escape_string(htmlspecialchars($_POST['title']));
       $description =                                                                                                              mysql_real_escape_string(htmlspecialchars($_POST['description']));

        //Error check both fields
        if ($title == '' || $description == '')
        {
            $error = 'ERROR: Please fill in all Fields!';
        }
        else
        {
            //Update data to database
            mysql_query("UPDATE skill SET title='$title', description='$description' WHERE id='$id'")
            or die (mysql_error());
            $row = mysql_fetch_array($result);
        }
    }
    //POST create
    else
    {

        //Get data, making sure it is valid
        $title = mysql_real_escape_string(htmlspecialchars($_POST['title']));
        $description = mysql_real_escape_string(htmlspecialchars($_POST['description']));

        //Error check both fields
        if ($title == '' | $description == '')
        {
            $error = 'ERROR: Please fill in all Fields!';
        }
        else
        {
            //Save new skill to database
            mysql_query("INSERT skill SET title='$title', description='$description'")
            or die(mysql_error());
            $row = mysql_fetch_array($result);
        }
    }
  }
  //GET ID
  else
  {
    //Get the id value from URL 
    if (isset($_GET['id']) && is_numeric($_GET['id']) && $_GET['id'] > 0)
    {
        //Query DB
        $id = $_GET['id'];
        $result = mysql_query("SELECT * FROM skill WHERE id=$id") or     die(mysql_error());
        $row = mysql_fetch_array($result);

        if($row)
        {
            $title = $row['title'];
            $description = $row['description'];
        }

    }
    //GET list
    else
    {
        $result = mysql_query("SELECT * FROM skill") or die(mysql_error());
        $row = mysql_fetch_array($result);
    }
}

//Encode data and display with JSON
echo json_encode($row);
?>

presumably because $row is only a reference to a single row. you need to create an array and populate it with while ($row = mysql_fetch_array($result)) — David John Welsh
– David John Welsh, Commented May 30, 2012 at 12:44
Please don't use mysql_* functions in new code. They were removed from PHP 7.0.0 in 2015. Instead, use prepared statements via PDO or MySQLi. See Why shouldn't I use mysql_* functions in PHP? for more information. — tereško
– tereško, Commented May 30, 2012 at 12:48

n00dle · Accepted Answer · 2012-05-30 12:43:11Z

2

mysql_fetch_array only pulls one row at a time from the database, you have to iterate over it in a loop like so:

<?php
$rows = array( ); // Initialise an empty array
while( $row = mysql_fetch_array( $result ) ) { // Loop over the database iterator
    $rows[] = $row; // Append the row to the array of rows
}

echo json_encode( $rows ); // Output the json encoded array of rows
?>

For more information, please see the PHP man page: http://php.net/manual/en/function.mysql-fetch-array.php

answered May 30, 2012 at 12:43

n00dle

6,0632 gold badges40 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

David John Welsh Over a year ago

d'oh... quicker response and a link to more resources. my hat goes off to you, sir....

n00dle Over a year ago

Yep, see my comment on your answer. I just looked it up as I've always used the [] format over array_push and was curious what the difference was, if any.

Jordan Vieira Over a year ago

Hey I used some of your code above and it worked. Thanks ianhales

David John Welsh · Accepted Answer · 2012-05-30 12:45:48Z

0

$array_of_rows = array();

while ($row = mysql_fetch_array($result)) {
  array_push($array_of_rows, $row);
}

echo json_encode($array_of_rows);

... or some such.

answered May 30, 2012 at 12:45

David John Welsh

1,5691 gold badge16 silver badges23 bronze badges

2 Comments

n00dle Over a year ago

php.net/manual/en/function.array-push.php recommends against using array_push for appending a single array as it has a small overhead associated with calling a function. It's better to use $array_of_rows[] = $row;.

David John Welsh Over a year ago

wow... makes perfect sense. cheers! although I feel oddly like a hijacker, asking a question and receiving an answer in the comments of a completely different question... :-)

Collectives™ on Stack Overflow

PHP, database Get and Post

2 Answers 2

3 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related