CODEWITHSUNDEEP
phpmysqldatabase
DMor
DMor

Reputation: 789

Having trouble getting information from MySQL using PHP

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:

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

Upvotes: 0

Views: 110

Answers (3)

OptimusCrime
OptimusCrime

Reputation: 14863

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

Upvotes: 1

Gianpaolo Di Nino
Gianpaolo Di Nino

Reputation: 1147

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.

Upvotes: 1

Ruthenium.
Ruthenium.

Reputation: 11

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

Upvotes: 1

Related Questions