push a PHP variable's value into an array created by mysql_fetch_array

Question

My code fetches several fields from a row in MySQL, makes a MYSQL_ASSOC array via mysql_fetch_array(), and formats it to JSON to return to Javascript in the browser. This part works. I want to additionally insert a PHP generated variable and its value to describe the time. A field exists for this variable and the timestamp code works in another script to insert the time of the original first post. In this script, I want the value to be the time a second PHP update script runs, not the original time value in the database. Despite a lot of trying, I can't get this last part to work. Here's relevant code:

$timeStamp = date("Ymd");
$userIP = $_SERVER['REMOTE_ADDR'];
$postingID = "$timeStamp-$userIP-u";
$latestUpdateID = str_replace(".", "", "$postingID")

//some pre-processing to make $fields_sql, an array to use in the SELECT

$fetch = mysql_query("SELECT $fields_sql FROM residence WHERE propertyID = '$propertyID'");
if( mysql_num_rows( $fetch ) ){
    while ( $row = mysql_fetch_array( $fetch, MYSQL_ASSOC ) ){
        for( $i = 0; $i < count( $propertyFields ); $i++ ) {
            $row_array[ $propertyFields[$i] ] = mysql_real_escape_string( $row[ $propertyFields[$i] ] );
        }
        array_push( $return_arr,$row_array ); 
        //get [{"city":"Rochester","streetAddress":"100 Main", etc.}]
    }
    $propertyFields[ 'latestUpdateID' ] = $latestUpdateID; //get "latestUpdateID":""
    array_push( $return_arr,$propertyFields[ 'latestUpdateID' ] ); //pushed in but no value
//want [{"city":"Rochester","streetAddress":"100 Main", "latestUpdateID":"20131107", etc.}]
} else { die('<li class=error>Ooops</li> }

echo json_encode( $return_arr );

Two comments: 1. As you can see the query returns only 1 row. The while loop is probably not needed, but I don't know how to do it any other way. Suggestions, that also fix the problem, would be doubly appreciated. 2. Yes, I know the mysql_ family of commands is deprecated. I have several of these and my next project is to convert them all to PDO, but at the moment, I understand PDO a lot less than this stuff.

What's your intent with that call to mysql_real_escape_string() in the for loop? That definitely isn't necessary, and potentially harmful to your data form if you're sending this down as JSON. json_encode() will handle the all the correct encoding. — Michael Berkowski
– Michael Berkowski, Commented Nov 7, 2013 at 21:07
Does $propertyFields have numeric or string keys? I see you treat it both ways. — geomagas
– geomagas, Commented Nov 7, 2013 at 21:10

jeroen · Accepted Answer · 2013-11-07 21:11:10Z

1

$row contains all the information you need and escaping using mysql_real_escape_string() is only needed when you output to a mysql database, so just do a:

if ($row = mysql_fetch_array( $fetch, MYSQL_ASSOC )) {
    $row['latestUpdateID'] = $latestUpdateID;
    echo json_encode( $row );
}

Edit: If more than just that row is supposed to be returned:

if ($row = mysql_fetch_array( $fetch, MYSQL_ASSOC )) {
    $row['latestUpdateID'] = $latestUpdateID;
    $return_arr[] = $row;
}
echo json_encode( $return_arr );

answered Nov 7, 2013 at 21:11

jeroen

91.8k22 gold badges117 silver badges132 bronze badges

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

4 Comments

Michael Berkowski Over a year ago

And don't forget to append $row onto $return_arr, which is the array holding multiple rows.

jeroen Over a year ago

@MichaelBerkowski You're probably right, it's a little unclear if more is returned in the echo, will edit...

Mike_Laird Over a year ago

Thanks, jeroen, your edited version with Michael Berkowski's input works like a charm. Thanks to Michael B, also. Its very simple code, so I think I can transform it to PDO as a next step. I'm interested in further thoughts about mysql_real_escape_string(). I have read that one should use it both inbound to the database, and outbound from a database, in case the database has been hacked with malicious code that would then get sent to the user's browser. What are your thoughts about the hacked and outbound-from-database scenario?

jeroen Over a year ago

@Mike_Laird No, you should always escape / encode for the medium you are outputting to, so if you output to html (the browser), you use htmlspecialchars(), no sql functions.

Iłya Bursov · Accepted Answer · 2013-11-07 21:13:54Z

0

As far as I understand you need to put $latestUpdateId into return_arr, check this code:

$fetch = mysql_query('SELECT '.$fields_sql.' FROM `residence` WHERE `propertyID`='.intval(propertyID, 10).' limit 1'); // note intval and limit 1
if( $row = mysql_fetch_array( $fetch, MYSQL_ASSOC ) ) {
    $row['latestUpdateID'] = $latestUpdateID;
    echo json_encode( $row );
}
else { die('<li class=error>Ooops</li>'); }

note: you do not need to use mysql_escape or mysql_real_escape functions, as you are not WRITE into database, you're READING from it

ps: single quotes are usually faster then double quotes in strings, intval is needed to prevent SQL injection and limit 1 usually faster if propertyID is not PK

answered Nov 7, 2013 at 21:13

Iłya Bursov

24.4k4 gold badges36 silver badges68 bronze badges

3 Comments

Mike_Laird Over a year ago

I tried your restatement of $fetch, and the PHP script did not work. In looking at it, I suspect problems with single and double quotes. I will tinker with it. Thanks for the security thought.

Mike_Laird Over a year ago

Ilya, the PHP syntax that works for me is $fetch = mysql_query("SELECT $fields_sql FROM residence WHERE propertyID = '".(int)$propertyID."' LIMIT 1"); I understand the security role of int() limits the variable to an integer. I understand LIMIT allows only 1 response. But I don't get the security role of LIMIT in this case. What is it protecting against here in an attempted SQL injection?

Iłya Bursov Over a year ago

@Mike_Laird limit 1 usually faster if propertyID is not PK it is not about security

Collectives™ on Stack Overflow

push a PHP variable's value into an array created by mysql_fetch_array

2 Answers 2

4 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related