How do I solve this undefined index error when using PHP to query my MySQL Database?

Question

I am learning PHP through the New Boston Youtube video tutorials.

When I run the query in the PHP script (script is below error message) on my localhost, I get an error message that is repeated twice. Please error message see below.

I want to be able to run this query within the PHP script and return the information I queried for.

Error message when I run index.php:

Notice: Undefined index: calories in /Applications/XAMPP/xamppfiles/htdocs/Database_To_Server/index.php on line 10

Notice: Undefined index: calories in /Applications/XAMPP/xamppfiles/htdocs/Database_To_Server/index.php on line 10

Code:

index.php

<?php
require 'connect.inc.php';

$query = "SELECT 'food' 'calories' FROM `food` ORDER BY 'id'";

if ($query_run = mysql_query($query)) {

    while ($query_row = mysql_fetch_assoc($query_run)) {
        $food = $query_row['food'];
        $calories = $query_row['calories'];
    }

} else {
    echo mysql_error();
}

?>

connect.inc.php

<?php
$conn_error = "Could not connect."; 

$mysql_host = 'localhost';
$mysql_user =  'root';
$mysql_pass = '';

$mysql_db = 'a_database';

if (!@mysql_connect($mysql_host, $mysql_user, $mysql_pass) || !@mysql_select_db($mysql_db)) {
    die($conn_error);
} 
?>

Solution 1

You're using the wrong identifiers for (and a missing comma between column names)

$query = "SELECT 'food' 'calories' FROM `food` ORDER BY 'id'";

do / and, remove the ' from about id

$query = "SELECT `food`, `calories` FROM `food` ORDER BY id";

• IF food isn't part of your columns (which seems to be the name of your table)

do

$query = "SELECT `calories` FROM `food` ORDER BY id";

• just an insight.

---

Footnotes:

Your present code is open to SQL injection.
Use prepared statements, or PDO with prepared statements.

---

Edit

To fix your present query, do:

require 'connect.inc.php';

$query = "SELECT `food`, `calories` FROM `food` ORDER BY `id`"; 

$result = mysql_query($query) or die(mysql_error());

while($query_row = mysql_fetch_assoc($result)){
    echo $query_row['food']. " - ". $query_row['calories'];
    echo "<br />";
}

---

As a learning curve, you should use mysql_error() to your advantage instead of just showing Could not connect., should there be a DB connection problem, therefore will not show you what the real error is.

For example:

<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
echo "Connected to MySQL<br />";
mysql_select_db("a_database") or die(mysql_error());
echo "Connected to Database";
?>

or from the manual - mysql_error()

<?php
$link = mysql_connect("localhost", "mysql_user", "mysql_password");

mysql_select_db("nonexistentdb", $link);
echo mysql_errno($link) . ": " . mysql_error($link). "\n";

mysql_select_db("kossu", $link);
mysql_query("SELECT * FROM nonexistenttable", $link);
echo mysql_errno($link) . ": " . mysql_error($link) . "\n";
?>

The above example will output something similar to:

1049: Unknown database 'nonexistentdb'
1146: Table 'kossu.nonexistenttable' doesn't exist

Solution 2

Change ' to ` for column and table name, and better use mysqli_query since mysql_query is deprecated

You're food and calories variable are overwritten at each loop iterations

<?php
require 'connect.inc.php';

$query = "SELECT `food` `calories` FROM `food` ORDER BY 'id'";

if ($query_run = mysqli_query($query)) {

    while ($query_row = mysqli_fetch_assoc($query_run)) {
        $food[] = $query_row['food']; // Food and calories variable transmored into an array
        $calories[] = $query_row['calories'];
    }

} else {
    echo mysqli_error();
}

?>

connect.inc.php

<?php
$conn_error = "Could not connect."; 

$mysql_host = 'localhost';
$mysql_user =  'root';
$mysql_pass = '';

$mysql_db = 'a_database';

if (!@mysqli_connect($mysql_host, $mysql_user, $mysql_pass) || !@mysqli_select_db($mysql_db)) {
    die($conn_error);
} 
?>

Comments

[Edward]

You are missing a comma and parenthesis in this line "SELECT 'food' 'calories' FROM food ORDER BY 'id'";" it is also highly recommended to wrap table names and column names in back ticks

[Raphael Müller]

you should use the mysqli_* functions instead of mysql_*

[jeroen]

By the way, apart from the answers, this example code is not going to get you very far as you are overwriting your variables inside the loop.

[Dustin Henrich]

Thank you for the comments, everyone. @RaphaelMüller, I'm going to look into that, but could you tell me why that function is better? Still pretty new into PHP. I've only been at this for a few weeks.

[Dustin Henrich]

@jeroen, right. I am still continuing the tutorial, but since I got the error message (above), I had to solve that first before continuing on. Thanks for the feedback!

[Funk Forty Niner]

@jeroen I saw that too, but am on the phone right now lol

[Kermit]

There is no more support for mysql_* functions, they are officially deprecated, no longer maintained and will be removed in the future. You should update your code with PDO or MySQLi to ensure the functionality of your project in the future.

[Raphael Müller]

your insight is maybe wrong, because the OP used to query food in his while loop.

[Funk Forty Niner]

@RaphaelMüller I have to think about any possibilities. Hence the if.

[Dustin Henrich]

Thank you. Food is actually one of the columns, but I am starting to think renaming it to something different from the table name might be more logical. Your answer fixed my problem. Thank you very much!

[Dustin Henrich]

Thank you. I looked at using isset(), but I know I have data coming from my database, so it did not seem necessary to check if the variables were set. But perhaps I do not have a complete understanding of the isset() function. Anyway, thank you for your feedback

[Lauri Orgla]

It is always a good practice to test if a value is present before using it. Since something could happen to the database connection or the database structure might change.

[Funk Forty Niner]

@DustinHenrich I was on the phone so I wasn't able to fix your present query. Have a look under my Edit at the bottom. But do look into the links I've given in my answer in regards to prepared statements.

[Dustin Henrich]

@Fred-ii-Thank you for the links and fixing my query. I'm going to look that over. I appreciate it!