Sql Query -- Where Have I Gone Wrong?

[phantom552]

I can't seem to get this thing to work.... 2 hours I've been tinkering with it to no success. The query itself seems to run, but when I try to echo the results nothing happens aside from the 'hello' & HTML code echos. Anyone see a blatant flaw in my code? the SKUID & DBROW echos are a test to make sure theyre set when I load the page, and they are perfect. I should also mention that dbrow is actually a column of the MySQL database, im not sure why I went with 'row' in the variable.

 

include 'php/opendb.php';
echo $skuid;
echo $dbrow;
mysql_select_db("brownells_api_data") or die(mysql_error());

$query = "SELECT * FROM Ammunition WHERE $dbrow = $skuid" or die(mysql_error());
$sql = mysql_query($query);
$row = mysql_fetch_array($sql);
$prodimg = $row['Thumbnail Image'];
echo $row['Thumbnail Image'];
echo "<div class=toparea>";
echo "<div class=imgarea>";
echo "$prodimg";
echo "</div>";
echo "</div>";
echo $row['Description'];
echo 'hello';

Edited October 30, 2012 by phantom552

[phantom552]

Also this is run in a local development environment where I have full server access & control, if that information helps anybody.

Edited October 30, 2012 by phantom552

[PFMaBiSmAd]

Putting - or die(mysql_error()); on the end of the $query = "...." string assignment statement doesn't tell you anything, since a string assignment statement doesn't have anything to do with mysql or a mysql_error. You would want to put the or die(....); statement on the end of your mysql_query() statement.

 

Also, you should have php's error_reporting set to E_ALL and display_errors set to ON in your master php.ini on your development system so that php will help you by reporting and displaying all the errors it detects. Stop and start your web server to get any changes made to the master php.ini to take effect and confirm that the two settings got changed by using a phpinfo statement in case the php.ini that php is using is not the one that you changed.

[sumpygump]

First, you should set your die() to the next statement. The $query = "..."; statement is just setting a string and will not evaluate to false. Like so:

$query = "SELECT * FROM Ammunition WHERE $dbrow = $skuid";
$sql = mysql_query($query) or die(mysql_error());

 

I am not sure of what the values of $dbrow and $skuid are or what are the field names in the table, so I don't know what might be going wrong with the actual data returned.

 

After $row = mysql_fetch_array($sql); add a line with var_dump($row); to see what the value of $row is.

[ManiacDan]

You say nothing happens, so there's two options:

 

1)  both $dbrow and $skuid are empty legitimately and you need to debug your included code.

 

2)  You have errors turned off and most of this code isn't working.  When you echo those variables, nothing happens...except maybe warnings about how the variables don't exist.

 

Put this at the top of your script:

error_reporting(E_ALL);

[codydaig]

You have a mixture of trying to handle multiple rows returned, a single row returned, and only god knows what. So.... I would recommend:

 

mysql_result($query, $x, "ID");

 

 

include 'php/opendb.php';

echo $skuid;

echo $dbrow;

mysql_select_db("brownells_api_data") or die(mysql_error());

 

$query = "SELECT * FROM Ammunition WHERE $dbrow = $skuid" or die(mysql_error());

$sql = mysql_query($query);

// You Do Not Need this row

// $row = mysql_fetch_array($sql);

$prodimg = mysql_result($query, $x, "Thumbnail Image");

echo mysql_result($query, $x, "Thumbnail Image");

echo "<div class=toparea>";

echo "<div class=imgarea>";

echo "$prodimg";

echo "</div>";

echo "</div>";

echo mysql_result($query, $x, "Description");

echo 'hello';

[ManiacDan]

Don't use mysql_result, it's a slower alternative to mysql_fetch_array

[phantom552]

Adding a var_dump line greets me with:

 

resource(7) of type (mysql result)

 

additionally, the 2 variables are not set in the code shown, they are set earlier and echoed, $dbrow being the title of the column I want to query ("SKU") and $SKUID being a product SKU carried over from the previous page in the URL & retrieved with $_Get

 

To be clear, I am attempting to retrieve data (Product photos, descriptions, & such) from a single row of my 'Ammunition' table in which the value of the SKU colum matches the value stored in $SKUID. I apologise if I sound like an idiot, but I just started using PHP yesterday, so this is a learning experience.

Edited October 30, 2012 by phantom552

[ManiacDan]

You said nothing happened aside from the HTML output.  Did you forget about the other echo lines?  Are they working or not?

 

Echo the query.  Run the query by hand in the database.

 

If your SKU has letters in it, you need to quote it in the query.

[phantom552]

OK, sorry for the ambiguity.

 

Here is what I currently see on-page:

 

105201030-105200989-20256

SKU

bool(false)hello

 

Which are the variable echos, the var_dump line, and "hello" echo.

 

If I run the SQL query directly in my database as "SELECT * FROM Ammunition WHERE SKU = 105201030-105200989-20256", I get nothing but:

 

MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0003 sec )

 

Which I find odd since if I enter PHPMyAdmin and open the table in question, the 4th row of the table has "105201030-105200989-20256" in the SKU column. All in all, I assume my query syntax is fubar somewhere.

[Pikachu2000]

By not enclosing the value in quotes, you're causing a mathematical operation to be performed in the SKU = comparison.

[sumpygump]

The problem is that you need quotes in your SQL query:

$query = "SELECT * FROM Ammunition WHERE $dbrow = '$skuid'";

 

So the resulting SQL must be:

SELECT * FROM Ammunition WHERE SKU = '105201030-105200989-20256';

 

This is because your SKUs contain non-numeric characters (-).

Edited October 30, 2012 by sumpygump

[phantom552]

Got it! Thanks for the brainstorming session!

[phantom552]

Sumpygump, I came to that conclusion right before reading your post lol. I shouldve just ran the query inside PHPmyA to begin with. Oh well, live and learn

[Pikachu2000]

The query would still succeed, so you wouldn't have gotten any error message, just an empty results set. It still wouldn't be obvious why you weren't getting what you expected. The query that was executing was equivalent to SELECT * FROM Ammunition WHERE SKU = -20215.

[Christian F.]

Also, don't forget to use mysql_real_escape_string () on the value, when inserting it into the SQL sentence. Otherwise you're open to SQL injection attacks.

 

You should also validate the input on retrieval, and inform the user if it's invalid. To do this you'll want to use a Regular Expression, to ensure that the input conforms to the pattern you're expecting:

if (!preg_match ('/^\\d+\\-\\d+\\-\\d+\\z/', $skuid)) {
  // Validation failed, inform user and show the form anew.
}

 

If the three number sequences of the SKU ID has a minimum and/or maximum length, you can set it as well. This is done by replacing the plus sign (following the \\d) with a {#,#} quantifier. Where the # is to replaced by a number, in the min,max pattern. If the latter is not set to a number, it denotes that only the minimum length is required.

In other words \\d{8,} expects 8 or more digits, to be valid.

 

You can find more information about Regular Expressions on Regular-Expression.info.