[SOLVED] PHP wont fetch data from MySQL database

[RottNKorpse]

I have been staring at just a few lines of code for over an hour and I don't see a single thing wrong with it so I wanted to get some new eyes looking at this to possibly shine some light on the issue.

 

What I am wanting to do is simply fetch a single value from a table in my database, a table that only has 1 row and 4 columns. Then to insert that data into a variable and call the variable later in the file.

 

The problem is for some reason when I call for the data it doesn't retrieve anything.

 

// Database Connection
$dbcnx = @mysql_connect("$dbhost","$dbuname","$dbpass");
$dbselect = @mysql_select_db("$dbname");
if ((!$dbcnx) || (!$dbselect)) { echo "Can't connect to database"; }

// Fetching data from MySQL
$order_sql  = @mysql_fetch_row(mysql_query("SELECT * FROM ".$prefix."_table") or die('Query failed: ' . mysql_error()));
$order      = $order_sql['order'];

echo $order;

 

All of the variables for connecting to the database and the prefix are stored in a separate file and they are pulled from that file with no issue. I've even tested to see if it is even looking for the table correctly and it is because I deleted the table which caused it to tell me it didn't exist so I recreated it and still the same empty result.

 

Here is my MySQL structure

4 Columns & 1 Row

order - field2 - field3 - field4

manual - 0 - 0 - 0

 

That's it, nothing special in either the code or the table yet it still refuses to pull the data...any idea what it could be?

 

Thanks in advance

[AndyB]

order is a MySQL reserved word.  Avoid using reserved words as table or field names.

 

While testing, result the @ error suppression.

[RottNKorpse]

ahh...ok well I changed the variable and now I have

 

$song_order_sql  = @mysql_fetch_row(mysql_query("SELECT * FROM ".$prefix."_table") or die('Query failed: ' . mysql_error()));
$song_order      = $song_order_sql['song_order'];

echo $song_order;

 

yet it still wont work.

[jonsjava]

modified to work:

 

<?php
// Database Connection
$dbcnx = @mysql_connect("$dbhost","$dbuname","$dbpass");
$dbselect = @mysql_select_db("$dbname");
if ((!$dbcnx) || (!$dbselect)) { echo "Can't connect to database"; }
/* ##################################################### */
/* NEED TO DEFINE $prefix                                */
/* ##################################################### */

// Fetching data from MySQL
/* EDITED to clean it up and make it just easier to read*/
$sql = "SELECT * FROM ".$prefix."_table";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
$song_order_sql  = @mysql_fetch_row($result);
$song_order      = $song_order_sql['order'];
echo $song_order;

[RottNKorpse]

modified to work:

 

<?php
// Database Connection
$dbcnx = @mysql_connect("$dbhost","$dbuname","$dbpass");
$dbselect = @mysql_select_db("$dbname");
if ((!$dbcnx) || (!$dbselect)) { echo "Can't connect to database"; }
/* ##################################################### */
/* NEED TO DEFINE $prefix                                */
/* ##################################################### */

// Fetching data from MySQL
/* EDITED to clean it up and make it just easier to read*/
$sql = "SELECT * FROM ".$prefix."_table";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
$song_order_sql  = @mysql_fetch_row($result);
$song_order      = $song_order_sql['order'];
echo $song_order;

 

thanks for cleaning it up but it still doesnt give me any data...I've tested the sql in phpmyadmin and it is fine...the prefix variable is defined, on a separate file as I stated in the first post.

[jonsjava]

lets make sure it's building the query right:

<?php
// Database Connection
$dbcnx = @mysql_connect("$dbhost","$dbuname","$dbpass");
$dbselect = @mysql_select_db("$dbname");
if ((!$dbcnx) || (!$dbselect)) { echo "Can't connect to database"; }
/* ##################################################### */
/* NEED TO DEFINE $prefix                                */
/* ##################################################### */

// Fetching data from MySQL
/* EDITED to clean it up and make it just easier to read*/
$sql = "SELECT * FROM ".$prefix."_table;";
print $sql;
/*
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
$song_order_sql  = @mysql_fetch_row($result);
$song_order      = $song_order_sql['order'];
echo $song_order; */

if

$prefix = "cheese";

you should see:

SELECT * FROM cheese_table;

if you just see

SELECT * FROM _table;

your problem is $prefix

 

[RottNKorpse]

I appreciate your help jonsjava but I already stated the prefix variable works fine and is certainly not the issue but just to cooperate I hardcoded the prefix into the query and still same thing.

[jonsjava]

the only reason I mention it is everything else appears fine, and it should be echoing the result. try this then:

<?php
// Database Connection
$dbcnx = @mysql_connect("$dbhost","$dbuname","$dbpass");
$dbselect = @mysql_select_db("$dbname");
if ((!$dbcnx) || (!$dbselect)) { echo "Can't connect to database"; }
/* ##################################################### */
/* NEED TO DEFINE $prefix                                */
/* ##################################################### */

// Fetching data from MySQL
/* EDITED to clean it up and make it just easier to read*/
$sql = "SELECT * FROM ".$prefix."_table;";
$result = mysql_query($sql) or die('Query failed: ' . mysql_error());
while ($song_order_sql  = @mysql_fetch_assoc($result)){
$song_order  = $song_order_sql['order'];
echo $song_order;
echo "<br />";
}

this should print out a list of all items. lets see if you have data now.

[RottNKorpse]

that worked!!!...thanks dude...

 

I used your suggestion of using mysql_fetch_assoc instead of fetch_row and it worked.

 

now looks like

 

$sql             = "SELECT * FROM ".$prefix."_table";
$result          = mysql_query($sql) or die('Query failed: ' . mysql_error());
$song_order_sql  = mysql_fetch_assoc($result);
$song_order      = $song_order_sql['song_order'];
echo $song_order;

 

so thanks again dude.

[jonsjava]

if you can click on the checkbox to close it out, that would be appreciated.

[RottNKorpse]

oh ok I didnt see the solved button...will do and thanks again

[jonsjava]

Glad to help!