picking out a single value from a database

[ScrewLooseSalad]

I have only a little experience in interfacing PHP with MySQL, I'm trying to grab a single value from a database, specifically the name of an item stored with its details, in the following code I attempt to call the items name from the [Part_Name] column, for the given ID number in [PartID].

 

$query = "SELECT `Part_Name` FROM `".$list."` WHERE `PartID` = ".$partid.";";
$itemname = mysql_result(mysql_query($query), 0, 0);
echo "<p>This will delete the item: ";
echo $itemname;
echo ". Are you sure you want to do this?</p>";

 

The resulting line says: [This will delete the item: . Are you sure you want to do this?].

 

----------

 

Also, I tested that MySQL line and it output this:

 

+-----------+
| Part_Name |
+-----------+
| screen    |
+-----------+

 

if that helps at all, I've tried changing the row and field values in mysql_result() but haven't had any success, am I using it wrong?

 

----------

 

Can anyone point me in the right direction? Thanks

Edited February 8, 2013 by ScrewLooseSalad

[PFMaBiSmAd]

You should not nest mysql_ function calls, since that prevents you from testing for errors or even if the query matched anything. The following logic is what you should use for a SELECT query, every time you perform a query -

 

<?php
$query = "SELECT Part_Name FROM `$list` WHERE PartID = $partid"; // use back-ticks `` only when need and string concatenation only when needed
if(!$result = mysql_query($query)){
// query failed due to an error, handle that condition here...
// do any user error reporting here - i.e. "Sorry, the requested operation could not be performed.";
// do any application error reporting here...
trigger_error("Query failed: $query<br />Error: " . mysql_error()); // assumes you have php's error_reporting/display_errors/log_errors set appropriately
} else {
// query run without any errors, check if the query matched any row(s)
if(mysql_num_rows($result) < 1){
// no row matched, handle that condition here...
echo "The requested partid does not exist.";
} else {
// the query matched one or more rows, use the data here
list($itemname) = mysql_fetch_row($result); // mysql_result is the slowest way of accessing data, use a fetch_ statement
echo "<p>This will delete the item: $itemname. Are you sure you want to do this?</p>";
}
}

 

The above code has error checking logic (test if something failed or not), error reporting/logging logic (output a user message and report/log application error information, and error recovery logic (take an expected execution path when something doesn't work to prevent follow-on errors and trying to use non-existent data values.) When you use code like this, it will tell you exactly what your code is doing and point to where problems are at in it.

Edited February 8, 2013 by PFMaBiSmAd

[ScrewLooseSalad]

I think I understand, however, I still haven't got it to work, I tried reinterpreting your code, I tried it as is, and I've tried it bit by bit. It seems that

!$result = mysql_query($query)

always goes into the if statement, I tried without and it simply doesn't work, but the query appears to work, as I printed the values and they all seemed to come out ok...

[Zane]

The real solution to your question is the syntax of the mysql_result function. Sure, I agree that you should not nest mysql functions, but your code should still work regardless and I'm not gonng preach to you about "ideal coding practices". The number one step in figuring anything out in PHP is to look at it's very elaborately awesome manual.

 

You are currently using this code

mysql_result(mysql_query($query), 0, 0);

If you were to look at the manual for mysql_result

 

 

you would realize you should be using this syntax.

 

mysql_result(mysql_query($query), 0);

Why? because your query only selects ONE field. There is no point in specifying the third argument when you only have one field. It is optional

 

If you did want to specify the third argument, it would be easier to use the fieldname

 

mysql_result(mysql_query($query), 0, "thefieldname");

 

Theoretically, your use of the function should work, which says "mysql_result, first row, first column".

I don't have an answer for why that isn't working as expected but I know that using the fieldname will fix it.

Edited February 8, 2013 by Zane

[DaveyK]

I think I understand, however, I still haven't got it to work, I tried reinterpreting your code, I tried it as is, and I've tried it bit by bit. It seems that

!$result = mysql_query($query)

always goes into the if statement, I tried without and it simply doesn't work, but the query appears to work, as I printed the values and they all seemed to come out ok...

 

so what error is displayed then?

[ScrewLooseSalad]

so what error is displayed then?

 

SELECT command denied to user ''@'localhost' for table

I think its because I'm not using the log in details I've called earlier... I don't think I've actually logged into my database

Edited February 8, 2013 by ScrewLooseSalad

[ScrewLooseSalad]

when using query() I can just stick my credentials [$db] in like so [$result = $db->query($query);]

how can I stick my credentials into mysql_query()? I tried the same way and it didn't work.

I've looked into MDB2 and I didn't like the look of it, it must be possible to use it without any addons

[DaveyK]

Connect to a mysql database

 

// connect to the db
mysql_connect(database, username, password) or die(mysql_error());
mysql_select_db(table_name) or die(mysql_error());

[ScrewLooseSalad]

I still can't get this thing to retrieve the one value....

 

echo "begin<br>";
$query = "SELECT Part_Name FROM database.".$list.";";
$result = $db->query($query);
$row = $result->fetch_assoc();
echo htmlspecialchars(stipslashes($row[Part_Name]));
echo "<br>end";

 

I've put in the query into MySQL in the terminal, and it retrieves the name of the product I am trying to look up, but I simply cannot get php to print this value...

 

earlier I had some error checking

if(mysql_num_rows($result) < 1){
// no row matched,
echo "The requested partid [$partid] does not exist.";

which was always triggered, I output "mysql_num_rows($result)" and it output '1', which is not < 1, so I don't know why that is being triggered...

Edited February 20, 2013 by ScrewLooseSalad

[Jessica]

You shouldn't be striping slashes from data in your database because by the time it's in the database it shouldn't have any extra slashes.

You also spelled the function name wrong.

You need to use a string with quotes in your array key.

You need to check for SQL errors when doing any queries.

 

To debug this particular error, try a print_r on row immediately after the fetch.

[ScrewLooseSalad]

You shouldn't be striping slashes from data in your database because by the time it's in the database it shouldn't have any extra slashes.

You also spelled the function name wrong.

You need to use a string with quotes in your array key.

You need to check for SQL errors when doing any queries.

 

To debug this particular error, try a print_r on row immediately after the fetch.

 

superb, thanks for your help!

Thankyou to everyone who contributed, Its working great now!

Edited February 20, 2013 by ScrewLooseSalad