2

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);
} 
?>
Improve this question
7
  • 2
    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 Commented Sep 22, 2014 at 16:54
  • 1
    you should use the mysqli_* functions instead of mysql_* Commented Sep 22, 2014 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. Commented Sep 22, 2014 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. Commented Sep 22, 2014 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! Commented Sep 22, 2014 at 17:05

5 Answers 5

Reset to default
16

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

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

5 Comments

your insight is maybe wrong, because the OP used to query food in his while loop.
@RaphaelMüller I have to think about any possibilities. Hence the if.
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!
@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.
@Fred-ii-Thank you for the links and fixing my query. I'm going to look that over. I appreciate it!
1

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

Comments

0

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.

Improve this answer

Comments

0

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";

Improve this answer

Comments

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

?>
Improve this answer

2 Comments

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
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.