Having trouble getting information from MySQL using PHP

Question

I've been trying this for hours, but haven't had any luck. Every tutorial I find online gives a different method to connect to a MySQL database using PHP, so its hard to find consistency.

When I run the following code, I get "0" for the values I attempt to retrieve. How can I get the values? I have the following code so far:

<?php 
        //Connect to MySQL server
        $conn = mysql_connect('XXIP ADDRESSXX', 'XXUSERNAMEXX', 'XXPASSWORDXX') or die(mysql_error()); //Connect to mysql

        //Continue if connection successful
        if($conn != null) {
            $db_selected = mysql_select_db('itemdata', $conn) or die(mysql_error()); //Connect to database using the connection

            //Continue if selected database successfully
            if($db_selected == true) {
                //The statement used to query the database  
                $query = "SELECT * FROM item_prices";

                //Store the results of the query
                $result = mysql_query($query, $conn) or die(mysql_error()); //Retrieve all data from the table  

                //Convert to assoc array
                $values = mysql_fetch_assoc($result);

                $cost = $values['cost'];
                $retail = $values['retail'];
                $in_stock = $values['available'];

                                    echo("<p>$cost</p>");
                                    echo("<p>$retail</p>");
                                    echo("<p>$in_stock</p>");
            }
            else 
                echo "Unable to connect to database";
        }
        else
            echo "Unable to connect to MySQL Server";

        //Close database
        mysql_close($conn);
    ?>

Now for some further information: With debugging, I verified that the code reaches the inner most "if" statement, so It properly connects and selects the database. None of the errors get thrown since I just receive "0" for the 3 variables I attempt to retrieve. (I tried printing right after to verify it wasn't an issue with variable scope).

Any help would be great, I've never coded a connection to MySQL and i'm having trouble finding the best method (I found tutorials on mysqli, mysql, object oriented, etc... and none are complete (I looked at the user manual also)..

Thanks.

EDIT: The structure of the database is as follows (so far)

database name: itemdata

table name: item_prices

The table has 4 columns:

itemID (primary key) - this is what is searched for (type int)
cost (type float)
retail (type float)
instock (type boolean)

I'm trying to retrieve all the values in the table using itemID

FINAL EDIT I copied the table from PHPMyAdmin (this is all for test purposes, so the data is bs)

itemID  cost    retail  instock
0   0   0   0
1   100 150 1
2   200 250 1
3   300 350 0
4   400 450 0
5   500 550 1
10  100 150 1
11  200 250 1
12  300 350 0
13  400 450 0
14  500 550 1

Could you give us an idea of what your db looks like? I can see at least 3 of the columns, what type are they? Integer, etc... — Drewness
– Drewness, Commented Aug 9, 2012 at 22:25
I don't see any code to actually output the variables after they're assigned. — j08691
– j08691, Commented Aug 9, 2012 at 22:31
Like I mentioned, I removed that (but i'l re-add it just for test purposes in the above code) — DMor
– DMor, Commented Aug 9, 2012 at 22:33
as far as i read you use mysql_fetch_assoc expecting just one row.. you sure you got just 1 row in your item_prices table? you say you are searching an itemID but the query is generic "SELECT * FROM item_prices" where the "where" condition is? ;) — Gianpaolo Di Nino
– Gianpaolo Di Nino, Commented Aug 9, 2012 at 22:33
My original query was "SELECT 'cost' FROM 'item_prices' WHERE itemID = 4" — DMor
– DMor, Commented Aug 9, 2012 at 22:39

OptimusCrime · Accepted Answer · 2012-08-09 22:50:51Z

1

I've taken a little look at your code and I am pretty sure (like 95%) that this is working code. If it is still not working you have done something else wrong.

By the way, you don't need to echo an error-message when you use die(), the script will stop working from that point.

Here's my prettified code:

<?php 
//Connect to MySQL server
$conn = mysql_connect('XXIP ADDRESSXX', 'XXUSERNAMEXX', 'XXPASSWORDXX')
    or die('Unable to connect to MySQL Server');


// Select db
mysql_select_db('itemdata', $conn)
    or die('Unable to connect to database');

// Run the query and fetch the results
$query = "SELECT cost FROM item_prices WHERE itemID = 4";
$values = mysql_fetch_assoc(mysql_query($query));

// Echo everything
echo '<p>'.$values['cost'].'</p>';
echo '<p>'.$values['retail'].'</p>';
echo '<p>'.$values['available'].'</p>';

// Close the connection
mysql_close($conn);
?>

answered Aug 9, 2012 at 22:50

OptimusCrime

14.9k13 gold badges63 silver badges98 bronze badges

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

4 Comments

DMor Over a year ago

this is much better actually, but one question. When using mysql_query() dont you need to include the $conn (connection/link) as a parameter?

OptimusCrime Over a year ago

Unless you have more connections running, PHP will select the correct one for you :)

DMor Over a year ago

This is the best answer :), but for future observers, change values['available'] to values['instock']. Thanks

OptimusCrime Over a year ago

Thank you. The other people answer posted snippets you could use if you want to output several values from the database (like everything).

Ruthenium. · Accepted Answer · 2012-08-09 22:41:48Z

1

Since your query likely returns more than one row you need to put the fetch function in a while loop.

while ($row = mysql_fetch_assoc($result)) 
{
    echo $row["cost"];
    echo $row["retail"];
    echo $row["instock"];
}

Also according to your table description you have no attribute named "available".

answered Aug 9, 2012 at 22:41

Ruthenium.

112 bronze badges

1 Comment

DMor Over a year ago

I just realized the mistake with "available" its because I just modified it to "instock". Since this is almost identical to the previous post, i'm going to award him the answer. But thank you for the response

Gianpaolo Di Nino · Accepted Answer · 2012-08-09 22:49:13Z

1

1) you say you are searching for an itemId but the query got no "where" condition.

2) since your table has more than one row as a result you should use mysql_fetch_assoc in this way to get all rows:

// While a row of data exists, put that row in $row as an associative array
// Note: If you're expecting just one row, no need to use a loop
// Note: If you put extract($row); inside the following loop, you'll
//       then create $userid, $fullname, and $userstatus
while ($row = mysql_fetch_assoc($result)) {
    echo $row["userid"]; 
    echo $row["fullname"];
    echo $row["userstatus"];
}

if you expect just one result you can do something like this:

$row = mysql_fetch_assoc($result);
extract($row);

and you'll get all variables yet assigned with the same names you got inside your database.

edited Aug 9, 2012 at 22:49

answered Aug 9, 2012 at 22:41

Gianpaolo Di Nino

1,1375 silver badges17 bronze badges

2 Comments

DMor Over a year ago

If I change my query to "SELECT * FROM item_prices WHERE itemID = X", then use the associative array, i'll have just that one record?

DMor Over a year ago

I got it to work this way, thank you. I misunderstood what the mysql_fetch_assoc() returned. I thought it returned an associative array of the whole table

Collectives™ on Stack Overflow

Having trouble getting information from MySQL using PHP

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related