[Resolved] how do you pull a single field from the database?

jasonmills58

what am I doing wrong here? I set this up just to test my results but I'm not getting anything back. I think I'm using the wrong syntax or something. Ultimately, I want to un-comment the code at the bottom. This script deletes a file associated with a database record, then delete the record. The records store

clientid | reportname | filename | path

turns out i don't really need the path because i think unlink() works relatively and all my files are stored in the same place. Anyway, here's what I've got:

$query = "SELECT filename FROM reclamation WHERE reportname='$reportname' AND clientid='$id'";
if ( ! mysql_query( $query, $link ) ) {
	print mysql_error();
	return false;
}

$filename = mysql_result( $query );
print $filename;
//unlink(../uploads/$filename);

//$delete_record = "DELETE FROM reclamation WHERE reportname='$reportname' AND clientid='$id'";
//if ( ! mysql_query( $delete_record, $link ) ) {
//	print mysql_error();
//	return false;
//}

In regards to "$filename = mysql_result( $query );" I tested it with just $filename = $query and saw the query exactly as it should be. But then I tried mysql_num_rows to see if anything was there, mysql_fetch_row, mysql_fetch_field - everything I could think of from the manual. All I got were bad result resources and "Wrong parameter count for mysql_result() " I guess I'm just going about this wrong.

Thanks!

tomwerner

try doing an echo of the query variable there might be something wrong with putting the variables into it. I always use { } like this:

$query = "SELECT filename FROM reclamation WHERE reportname='{$reportname}' AND clientid='{$id}'";

You could also try copying that query, putting some valid values into the variable areas, remove the {} and try the query at the mysql commmand prompt (C:\mysql\bin\mysql.exe)

hth

jasonmills58

i appreciate that, the query is clean. I tested it. I'm just having trouble using the result...

[edit] i just tried mysql_query and got Resource id #3 from:

$filename = mysql_query( $query );
print $filename;

[/edit]

is stumped

also knows thats nothing new... :rolleyes:

keith73

problem is you are using mysql_result() incorrectly.

http://www.php.net/mysql_result

mysql_result ( resource result, int row [, mixed field])

so in this case you should assign the link identifier for the query to a variable. And when you get the result, you want the result from row 0.

$result = mysql_query($query);
$filename = mysql_result($result,0,'filename');

keith

ahundiak

Have you tried the example in the manual on the mysql_result page?

jasonmills58

thank you. I got this to work:

$query = "SELECT filename FROM reclamation WHERE reportname='$reportname' AND clientid='$id'";
if ( ! mysql_query( $query, $link ) ) {
	print mysql_error();
	return false;
}

$result =  mysql_query( $query );
$filename = mysql_result( $result, 0 );
print $filename;

unlink(../uploads/$filename);

This is great. I also have records that have 0-4 uploaded files associated with them. How I would I use a while loop to find the filenames and unlink them? forunately, they're all in the same directory, so thats not an issue. i just need the filenames.

thanks again!

keith73

but now you're doing the same query twice. very inefficient and can wear down your server.

try this instead:

$result =  mysql_query( $query );
if ( ! $result ) {
    print mysql_error();
    return false;
}

$filename = mysql_result( $result, 0 );
print $filename;

keith