CODEWITHSUNDEEP
phpmysqlsqldatabase
Dustin Henrich
Dustin Henrich

Reputation: 31

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

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

Upvotes: 2

Views: 23468

Answers (5)

Josh
Josh

Reputation: 1

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

Upvotes: 0

Funk Forty Niner
Funk Forty Niner

Reputation: 74217

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

Upvotes: 16

Azteca
Azteca

Reputation: 570

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.

Upvotes: 0

Isaac
Isaac

Reputation: 983

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

Upvotes: 1

Lauri Orgla
Lauri Orgla

Reputation: 561

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

?>

Upvotes: -2

Related Questions