PHP not displaying result from MYSQL query

Question

I have the following code

$sql = "SET @uid := (SELECT ID FROM channels WHERE Used = 0 ORDER BY RAND() LIMIT 1);";
$sql = "UPDATE channels SET Used = 1 WHERE ID = @uid;";
$sql = "SELECT * FROM channels WHERE ID IN = @uid;";
$result = mysqli_multi_query($conn, $sql)
                 or die( mysqli_error($sql) );
if (mysqli_num_rows($result) > 0) {
  $text = '';
  while($row = mysqli_fetch_assoc($result)) {  
      $Channel_Location = $row['Channel_Location'];
      $text =  $text . $Channel_Location;

    }       
}

Now the issue i'm having is the php isnt displaying the result returned by the MYSQL query which is stored in a session later on in the code to be displayed on a dummy page it comes up with the following error

Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result

The my SQL query does exactly what I need it to I just need to, so I don't really want to change it. I just need some advice on how i'd get the PHP to echo the @uid is there anyone willing to help me solve the issue? if so thankyou.

$result = mysqli_query($conn, $sql) or die(mysqli_error($conn)); — Funk Forty Niner
– Funk Forty Niner, Commented May 13, 2016 at 14:16
php.net/manual/en/mysqli.multi-query.php you have 3 queries in your $sql so you should use multi_query function — Alex
– Alex, Commented May 13, 2016 at 14:18
I agree with @Fred-ii- you have errors, which is why that line is reached with a non mysqli_result parameter. — CodeGodie
– CodeGodie, Commented May 13, 2016 at 14:22
So edited the code to $result = mysqli_multi_query($conn, $sql); and still no luck — Martin Beardmore
– Martin Beardmore, Commented May 13, 2016 at 14:23
Your first query generates a result set, not a single ID, which you store in @uid. Then your second query (UPDATE) uses this value as if it were a single ID, but this is not true. I believe you should change ID = @uid to **ID IN @uid" and this could solve your problem. — Edvaldo Silva de Almeida Jr
– Edvaldo Silva de Almeida Jr, Commented May 13, 2016 at 14:30

Alex · Accepted Answer · 2016-05-16 15:30:46Z

1

You have 3 queries in your $sql so you should use multi_query function http://php.net/manual/en/mysqli.multi-query.php

And you can change your first query to:

SET @uid = 0;
SELECT @uid := ID FROM channels WHERE Used = 0 ORDER BY RAND() LIMIT 1);

Update You can try this fragment of your code modified with all commented improvements.

$sql = 'SET @uid = 0;';
$sql .= 'SELECT @uid:= ID FROM channels WHERE Used = 0 ORDER BY RAND() LIMIT 1);';
$sql .= 'UPDATE channels SET Used = 1 WHERE ID = @uid;';
$sql .= 'SELECT * FROM channels WHERE ID IN = @uid;';
if (mysqli_multi_query($conn, $sql)) {
   do {
       $result = mysqli_store_result($conn);
   } while(mysqli_next_result($conn));
   if (mysqli_num_rows($result) > 0) {
     $text = '';
     while($row = mysqli_fetch_assoc($result)) {  
       $Channel_Location = $row['Channel_Location'];
       $text =  $text . $Channel_Location;
     }       
   }
} else {
  die( mysqli_error($conn) );
}

edited May 16, 2016 at 15:30

answered May 13, 2016 at 14:41

Alex

17.3k1 gold badge31 silver badges52 bronze badges

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

10 Comments

Martin Beardmore Over a year ago

Can i be cheeky im rather new to this could you update my code to explain what you mean?

GertG Over a year ago

Your changes have introduced a new problem. You're replacing the sql each time instead of adding to it. (The second and third $sql = need to be $sql .=.)

Martin Beardmore Over a year ago

Hey alex sorry about the late reply, i've tested this code but comes up with 2 errors saying the $result is an undefined variable in if (mysqli_num_rows($result) > 0) { and mysqli_num_rows() expects parameter 1 to be mysqli_result,

Alex Over a year ago

are you sure you copied my code? the issue could be if you changed this part while(mysqli_next_result($conn)) $result = mysqli_store_result($conn); I mean there is no { after while... condition. Are you sure you did not add that brace?

Martin Beardmore Over a year ago

Yeah literally copied it straight over? and never changed a thing and I've just copied it over again and still no luck with it still displays the same errors

|

Collectives™ on Stack Overflow

PHP not displaying result from MYSQL query

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related