CODEWITHSUNDEEP
phpmysqldatabase
Kelsey Abreu
Kelsey Abreu

Reputation: 1124

Receiving content in a mysqli_result

I'm trying to replicate this code from Java into php but I can't exactly figure out how to get the contents of the row/column?

This is the java code:

ResultSet resultsRS = statement.executeQuery("select distinct snum from shipments where quantity >= 100");

int rowCount=0;

while(resultsRS.next()){
    statement2.executeUpdate("UPDATE suppliers SET status = status + 5 WHERE snum = "+"\""+resultsRS.getString(1)+"\"");
    rowCount++;  
}

So far I have this in php:

$result2 = mysqli_query($database,"select distinct snum from shipments where quantity >= 100");

$rowCount = 0;

foreach($result2 as $shipment){
    mysqli_query($database,"UPDATE suppliers SET status = status + 5 WHERE snum = "."\"".$shipment."\"");
    rowCount++;
}

This does not work as intended though.

Also if you notice all I want is one specific column to update.

EDIT: Got it to work

For everyone reading I was able get what I wanted with the following code:

for($counter=0; $row = mysqli_fetch_row($result2);$counter++){              
   foreach($row as $key=> $value){  
       mysqli_query($database,"UPDATE suppliers SET status = status + 5 WHERE snum = "."\"".$value."\"");
       $rowCount++;
   }                            
}

Upvotes: 0

Views: 251

Answers (3)

kander
kander

Reputation: 4286

The "best practice" solution to handle such a case is to first "prepare" the query, outside of the loop, then bind parameters to it during the loop. This avoids SQL injections; while in this specific case that might not be a concern, it's still good to get into the practice of not injecting data in your queries directly, but always using parameter binding. It also looks a lot nicer, easier to understand, without all that quoting nonsense :)

$result2 = mysqli_query($database,"select distinct snum from shipments where quantity >= 100");
$query = mysqli_prepare($database, 'UPDATE suppliers SET status = status + 5 WHERE snum = ?');
// Check for errors. This is obviously not the right way to handle this when going into production...
if(!$query) {
    var_dump(mysqli_error($database));
    die();
}
foreach($result2 as $shipment){
    $snum = $shipment['snum'];
    $query->bind_param('s', $snum);
    $query->execute();
    $rowCount++;
}

You might also want to optimize this by first collecting all the IDs, then issue a single update statement for the whole batch of IDs.

Furthermore, I'm expecting you'll be able to do this using a single query, performing an update on a join... so if performance is any consideration, you might want to look into that as well.

Upvotes: 0

fejese
fejese

Reputation: 4628

As you can read it on the php.net site (http://php.net/manual/en/mysqli.query.php) mysqli_query returns a mysqli_result object (http://www.php.net/manual/en/class.mysqli-result.php).

You have to fetch the rows from the result with one of the fetch_* functions.

For example:

$result2 = mysqli_query($database,"select distinct snum from shipments where quantity >= 100");

$rowCount = 0;

while($shipment = $result2->fetch_assoc()){
    mysqli_query($database,"UPDATE suppliers SET status = status + 5 WHERE snum = "."\"".$shipment['snum']."\"");
    rowCount++;
}

Upvotes: 1

Dawid Sajdak
Dawid Sajdak

Reputation: 3084

You should read PHP MYSQLI documentation:

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

/* check connection */
if (mysqli_connect_errno()) {
    printf("Connect failed: %s\n", mysqli_connect_error());
    exit();
}

$query = "SELECT Name, CountryCode FROM City ORDER by ID DESC LIMIT 50,5";

if ($result = $mysqli->query($query)) {

    /* fetch associative array */
    while ($row = $result->fetch_assoc()) {
        printf ("%s (%s)\n", $row["Name"], $row["CountryCode"]);
    }

    /* free result set */
    $result->free();
}

/* close connection */
$mysqli->close();
?>

from Mysqli fetch

Upvotes: 1

Related Questions