[SOLVED] mysql giving me a headache

[lewis987]

i have a mysql database to check what files have been uploaded by who in a table. The table consits of of these columns:

id, file, downloads, size, owner

 

My problem is when i attempt to get the rows from the database it doesnt show anything but i have a row with the following data:

id = 0

file = tt.txt

downloads = 0

owner = admin

size = 34

 

This is the code:

$querysql3 = "SELECT * FROM $files WHERE `owner` = 'admin'";
echo $querysql3;
$query3 = mysql_query($querysql3);
?>
<table border="0" cellspacing="0" cellpadding="0">
<?PHP
	while($row1 = mysql_fetch_array($query3)){
	$file_id = $row1['id'];
	$file_name = $row1['file'];
	echo "<tr>";
	echo "<td>" . $row1['id'] . "</td>";
		echo "<td>" . $row1['file'] . "</td>";
	echo "<td>" . $row1['size'] . "</td>";
	echo "<td>" . $row1['downloads'] . "</td>";
	echo "<td>" . '<form action="" method="post" name="zz">
		<input name="file_name" type="hidden" value="<?PHP echo $file_name; ?>" />
		<input name="file_id" type="hidden" value="<?PHP echo $file_id; ?>" />
		<input name="delete" type="submit" value="Delete?" /></form>' . "</td>";
	echo "</tr>";
	}
?>
</table>
<?PHP
}
?>

 

$files is defined earlier in the page. The sql is perfectly fine, i checked with phpMyAdmin 2.10.1, just the while loop isnt working, thanks in advance

[MadTechie]

can you post the echoed SQL statement

[lewis987]

sure, here:

 

SELECT * FROM drw_uploader_files WHERE `owner` = 'admin'

 

Thats the sql, cant find the damn error anywhere

[MadTechie]

Humm

 

OK try

 

change

while($row1 = mysql_fetch_array($query3)){

to

while($row1 = mysql_fetch_array($query3, MYSQL_ASSOC)){

 

if this fails

try

die("linetest"); //check to see if it gets here
while($row1 = mysql_fetch_array($query3, MYSQL_ASSOC)){

[lewis987]

ok, i tried the code, it didnt fail, but i did try the die() in the while loop and it isnt showing the text  ???

 

[MadTechie]

in that case the problem isn't their..

 

the code isn't parsing that far.. check the if statements

 

or post more code

[lewis987]

here is the whole page:

 

<?PHP

include 'db.inc.php';
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);
$files = $dbprfx . "files";
if($_POST['delete']){
	$file = $_POST['file_name'];
	$id = $_POST['id'];
	$query1 = mysql_query("SELECT * FROM $files WHERE id = '$id'");
	if(mysql_num_rows($query1) == 1)
	{
		$query2 = mysql_query("DELETE FROM $files WHERE `id` = '$id' LIMIT 1");
		if(!$query2){
			echo "cannot find the specified file in the database.";
		}
		else
		{
			$folder = "files/ " . $id . "/" . $file;
			unset($folder);
			echo "File deletetion complete!";
			?>
                <meta http-equiv="refresh" content="5; url=?myAccount" />
                <?PHP
		}
	}
}
else
{
$querysql3 = "SELECT * FROM `drw_uploader_files` WHERE `owner` = 'admin';";
echo $querysql3;
$query3 = mysql_query($querysql3);
?>
<table border="0" cellspacing="0" cellpadding="0">
<?PHP
	while($row1 = mysql_fetch_array($query3, MYSQL_ASSOC)){
	$file_id = $row1['id'];
	$file_name = $row1['file'];
	echo "<tr>";
	echo "<td>" . $row1['id'] . "</td>";
		echo "<td>" . $row1['file'] . "</td>";
	echo "<td>" . $row1['size'] . "</td>";
	echo "<td>" . $row1['downloads'] . "</td>";
	echo "<td>" . '<form action="" method="post" name="zz">
		<input name="file_name" type="hidden" value="<?PHP echo $file_name; ?>" />
		<input name="file_id" type="hidden" value="<?PHP echo $file_id; ?>" />
		<input name="delete" type="submit" value="Delete?" /></form>' . "</td>";
	echo "</tr>";
	die("linetest"); //check to see if it gets here
	}
?>
</table>
<?PHP
}
?>

 

if i put the die() after the while loop then it shows, but if i put it inside the loop it doesnt show as if it doesnt show anything even though there is definitely something in the database.

[lewis987]

oops, posted the less uptodate code, here is the latest:

<?PHP

include 'db.inc.php';
mysql_connect($dbhost,$dbuser,$dbpass);
mysql_select_db($dbname);
$files = $dbprfx . "files";
if($_POST['delete']){
	$file = $_POST['file_name'];
	$id = $_POST['id'];
	$query1 = mysql_query("SELECT * FROM $files WHERE id = '$id'");
	if(mysql_num_rows($query1) == 1)
	{
		$query2 = mysql_query("DELETE FROM $files WHERE `id` = '$id' LIMIT 1");
		if(!$query2){
			echo "cannot find the specified file in the database.";
		}
		else
		{
			$folder = "files/ " . $id . "/" . $file;
			unset($folder);
			echo "File deletetion complete!";
			?>
                <meta http-equiv="refresh" content="5; url=?myAccount" />
                <?PHP
		}
	}
}
else
{
$querysql3 = "SELECT * FROM `drw_uploader_files` WHERE `owner` = 'admin';";
echo $querysql3;
$query3 = mysql_query($querysql3);
?>
<table border="0" cellspacing="0" cellpadding="0">
<?PHP
	if(mysql_num_rows($query3) != 1){
		echo "<tr>";
		echo "<td>";
		echo "<strong>You have no files uploaded!</strong>";
		echo "</td>";
		echo "</tr>";
	}
	else
	{
	while($row1 = mysql_fetch_array($query3, MYSQL_ASSOC)){
	$file_id = $row1['id'];
	$file_name = $row1['file'];
	echo "<tr>";
	echo "<td>" . $row1['id'] . "</td>";
		echo "<td>" . $row1['file'] . "</td>";
	echo "<td>" . $row1['size'] . "</td>";
	echo "<td>" . $row1['downloads'] . "</td>";
	echo "<td>" . '<form action="" method="post" name="zz">
		<input name="file_name" type="hidden" value="<?PHP echo $file_name; ?>" />
		<input name="file_id" type="hidden" value="<?PHP echo $file_id; ?>" />
		<input name="delete" type="submit" value="Delete?" /></form>' . "</td>";
	echo "</tr>";
	die("linetest"); //check to see if it gets here
	}
	}
?>
</table>
<?PHP
}
?>

[king arthur]

Put a die statement after your queries, e.g. $query3 = mysql_query($querysql3) or die(mysql_error()); and see if it is getting an error there.

[MadTechie]

add

echo "Found:".mysql_num_rows($query3);

before the

while($row1 = mysql_fetch_array($query3, MYSQL_ASSOC)){

 

just to check its finding records

[lewis987]

@king arthur:

 

im not getting an error with my sql, that new code has an if else statement to check whether the user has files uploaded or not. There are 3 rows in the database with the "owner" as admin. It is showing "You have no files uploaded" thats after it checks the database whether the user has files uploaded or not. Here is a picture of phpMyAdmin to prove there are files there.

 

 

@madTechie:

 

It says "found:0" take a look at the picture above, it shows that there are 3 rows with the username "admin"

[lewis987]

well, found something else, if i change everything to another table it works fine :S

[freakus_maximus]

Have you corrected this line when trying a different table?

 

$querysql3 = "SELECT * FROM `drw_uploader_files` WHERE `owner` = 'admin';";

 

Your string is being parsed as this:

 

SELECT * FROM `drw_uploader_files` WHERE `owner` = 'admin';

 

I am thinking that when you run the query currently it maybe treating your `owner` as being equal to "admin;".

 

Try removing the extra semicolon and see what happens.

 

 

[lewis987]

hmm... if you look at proper sql, it always has a semi-colon after it! so no matter what your query is, it should always have a semi-colon after it. but this is getting annoying, i changed it to the users database and i got "1admin2" (1 = userid; Admin = username; 2 = account type)

[lewis987]

damn, sorted! i put the data in the wrong database, i never checked the db.inc.php file