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);
} 
?>

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 — Edward, Sep 22 '14 at 16:54
you should use the `mysqli_*` functions instead of `mysql_*` — Raphael Müller, Sep 22 '14 at 16:55
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. — jeroen, Sep 22 '14 at 17:00
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, Sep 22 '14 at 17:05
@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! — Dustin Henrich, Sep 22 '14 at 17:05
There is **no more support** for `mysql_*` functions, they are [**officially deprecated**](https://wiki.php.net/rfc/mysql_deprecation), **no longer maintained** and will be [**removed**](http://php.net/manual/en/function.mysql-connect.php#warning) in the future. You should update your code with [PDO](http://php.net/pdo) or [MySQLi](http://php.net/mysqli) to ensure the functionality of your project in the future. — Kermit, Sep 24 '14 at 16:48

score 16 · Accepted Answer · edited May 23 '17 at 12:00

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

your insight is maybe wrong, because the OP used to query food in his while loop. — Raphael Müller, Sep 22 '14 at 16:56
@RaphaelMüller I have to think about any possibilities. Hence the *if*. — Funk Forty Niner, Sep 22 '14 at 16:57
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, Sep 22 '14 at 16:58
@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. — Funk Forty Niner, Sep 22 '14 at 17:19
@Fred-ii-Thank you for the links and fixing my query. I'm going to look that over. I appreciate it! — Dustin Henrich, Sep 24 '14 at 20:11

Isaac · Answer 2 · 2014-09-22T17:03:01.340

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);
} 
?>

score 0 · Answer 3 · answered Sep 22 '14 at 17:11

Well, to start you are missing a coma between the tables you are selecting in your query, then, you are using a deprecated class (Soon to disappear). If you are learning you should check the Dcoumentation of mysql_query, you should try instead mysqli.

Then you should get used to use grave for tables or field names and Normal apostrophe for Strings/VARCHAR or texts on inserts/updates/where etc.

Hope this helps.

score 0 · Answer 4 · answered Dec 19 '14 at 17:32

I happen to be following the same tutorial as you. It took me a bit to figure it out, but after looking at his example and comparing some answers from here, I was able to discern that when running a query you need to use the "`" instead of the single quotes.

(incorrect answer) $query = "SELECT 'food', 'calories' FROM 'food' ORDER BY 'id'";

(correct answer)
$query = "SELECT food, calories FROM food ORDER BY id";

score -2 · Answer 5 · answered Sep 22 '14 at 16:53

-2

Solution is isset() for checking if the array has the element requested. Also you had a flaw in your SQL. Fields which you are trying to select MUST be separated with a comma.

Example:

<?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 = isset($query_row['food'])?$query_row['food']:'';
        $calories = isset($query_row['calories'])?$query_row['calories']:'';
    }

} else {
    echo mysql_error();
}

?>

answered Sep 22 '14 at 16:53

Lauri Orgla

561
3
10

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 – Dustin Henrich Sep 22 '14 at 17:01
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. – Lauri Orgla Sep 22 '14 at 17:04

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

5 Answers5