CODEWITHSUNDEEP
phphtmlsqlarrays
pd93
pd93

Reputation: 1166

How to populate an array from data in a table

I'm writing a very simple seating plan arranger. I have a database with a list of people attending and each has a table number assigned ($tano)

My PHP is as follows:

$con = mysql_connect('localhost', $dbuser, $dbpass) or die(mysql_error());
$db = mysql_select_db($dbname, $con) or die(mysql_error());

// Get current table no
$tableno = $_GET["t"];
    
// Current table -> array
$t = array();
$i = 0;
$result = mysql_query('SELECT * FROM plan WHERE tano = $tableno ORDER BY fname');
while($row = mysql_fetch_array($result)) {
$t[$i] = $row;
$i++;
}
    
// Get other tables (Seats Remaining)
for ($i = 1; $i <= 40; $i++) {
    $result = mysql_query("SELECT * FROM plan WHERE 'tano' = $i");
    $seatsremaining = 10-mysql_num_rows($result);
         if ($seatsremaining == 0) {$d[$i] = "Table ".$i." (No Seats Remaining)";}
    else if ($seatsremaining == 1) {$d[$i] = "Table ".$i." (1 Seat Remaining)";}
    else if ($seatsremaining >= 2) {$d[$i] = "Table ".$i." (".$seatsremaining." Seats Remaining)";}
}

?>

The array is not populated and it generates an SQL error:

Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/greenbot/public_html/index.php on line 18

The table structure is:

enter image description here

Upvotes: 1

Views: 142

Answers (1)

George Cummins
George Cummins

Reputation: 28936

In your first query, you are missing quotes around your value:

$result = mysql_query('SELECT * FROM plan WHERE tano = $tableno ORDER BY fname');

This should be:

$result = mysql_query("SELECT * FROM plan WHERE tano = '$tableno' ORDER BY fname");

In your second query, you are using quotes instead of backticks around the column name:

$result = mysql_query("SELECT * FROM plan WHERE 'tano' = $i");

This should be:

$result = mysql_query("SELECT * FROM plan WHERE `tano` = $i");

You should note that your code assumes that the query completed successfully instead of checking. For debugging purposes, you can add:

... or die(mysql_error());

to the end of each of your mysql_query(...) statements to get details about the attempted queries. You should develop a logging strategy for such errors in production code.

Additionally, be aware that using unfiltered user input $tableno = $_GET["t"]; opens the door for SQL injection attacks. Consider updating your code to use parameterized PDO queries, or at least filter your incoming data.

Upvotes: 1

Related Questions