Php mysql passing variable by reference

I thought I could do this but the errors tell me otherwise.
Trying to do multiple queries through a loop using reference.

	for ($i = 1; $i < 7; $i++) {			
		$query&$i	= "SELECT draw_date, picknum0&$i from ".$table_name2." order by draw_id desc limit 20;";		
		$result&$i	= mysqli_query($link, $query&$i) or die ("Error in query: $query&$i. <br />" . mysqli_error($link) ."<br />");
		$numrows&$i = mysqli_num_rows($result&$i);			
		$row&$i		= mysqli_fetch_array($result&$i);			
	} // End for
	
	for ($i = 1; $i < 7; $i++) {
		echo "row&$i"."<br />";
		echo "<pre>"; print_r($row&$i); echo "</pre>";
	} // End for

The error is in the first query, Undefined variable: query and A non-numeric value encountered
Is there a mistake here or can it be done differently?

use an array instead of what you’re trying to do? Better yet, do it in one query and use array functions to handle it.

		$query	= "SELECT draw_date, picknum01, picknum02, picknum03, picknum04, picknum05, picknum06, picknum07 from ".$table_name2." order by draw_id desc limit 20;";		
		$result	= mysqli_query($link, $query) or die ("Error in query: $query. <br />" . mysqli_error($link) ."<br />");
		$numrows = mysqli_num_rows($result);			
		$row  = mysqli_fetch_all($result,MYSQLI_ASSOC); 		

        for ($i = 1; $i < 7; $i++) {
		echo "row$i"."<br />";
		echo "<pre>"; print_r(array_column($row,"picknum0".$i); echo "</pre>";
	}

(It’s been a while since I used mysqli instead of PDO… so forgive the edits as i try to remember the foibles of the library :P)

1 Like

Hi,

You have several problems here:

  • You use the $query variable without initializing it
  • You use string concatenation with an integer ($i)
    (you should use an array instead of concatenation)
  • You send 6 SELECT queries to the database, but you can fetch everything with a single query, that would be a lot more efficient
  • Mysqli is outdated and less secured than PDO, you really should use PDO instead.
    (for your information, I recently released this PDO database abstraction layer on Github)

Here’s a sample code without PDO to answer your question:

$query = "SELECT draw_date, picknum1, picknum2, picknum3, picknum4, picknum5, picknum6
          FROM " . $table_name2 . "
          ORDER BY draw_id DESC
          LIMIT 20";

$stmt = mysqli_prepare($link, $query);
if (!$stmt) {
    echo "Error preparing query: " . mysqli_error($link) . PHP_EOL;
    exit;
}

$result = mysqli_execute($stmt);
if (!$result) {
    echo "Error executing query: " . mysqli_error($link) . PHP_EOL;
    exit;
}

// Fetch all results at once
$rows = mysqli_fetch_all(MYSQLI_ASSOC, $result);

// Close resources
mysqli_free_result($result);
mysqli_stmt_close($stmt);
mysqli_close($link);

if ($rows) {
    foreach ($rows as $i => $row) {
        echo "**Row " . ($i + 1) . "**" . PHP_EOL;
        echo "<pre>";
        print_r($row);
        echo "</pre>";
    }
} else {
    echo "No data found in the last 20 drawings." . PHP_EOL;
}
  • Why are you preparing a query without any parameters in it?
  • Your code does not generate the requested output.
  • Your parameters for fetch_all are backwards.
1 Like

Hello m_hutley,
The query is extracting data from a db table.
The fetch_all is my typo error, it should be mysqli_fetch_array($result&$i);

Hello gillesmgliori,
Sorry, but the PDO question has been resolved.

I am wondering whether or not passing variable by reference using php with mysql as shown above can be done?

Post #4 was directed towards gillesmgliori, not you :wink:

What you’re trying to do isnt passing by reference, it’s creating a dynamic variable name. Which can be done. It’s just… never the right answer?

If you actually insist on doing this, the answer is using curly braces. ${'prefix'.$i}

(Note that you will probably not be able to use this mid-string without breaking and concatenating)

Thanks, I will rethink it.