how to return result as array?

chemoul78

Hi,

I'm a bit stuck.

I've got a table 'picture' in mysql that looks like this:

ID || imageno || filename

ID is unique and auto increment.
There are multiple rows with the same filename of a picture.

With the following script I'm trying to get all the imagenumbers (imageno) for a specific filename (in this case testpic.jpg) from the table.
I'm trying to get this result back into an array and then go through this array with a while loop, deleting one of the rows that contains the filename every time it passes through the loop.
The reason I would like to do it like this is because I need to read every imagenumber out of the array and into a new array for other operations afterwards.

The problem is that the "read into array" line only displays a value for the image number during the first iteration, but no values for the following interations.

What am I doing wrong?

$res = mysql_query("select imageno from picture where filename = 'testpic.jpg'");

$cn = 0;

while ($row=mysql_fetch_row($res))
{
$id = $row[$cn];
$idarray[$cn] = $id;
echo "read into array:".$idarray[$cn];
$cn++;
$deleteimage = mysql_query("delete from picture where filename = 'testpic.jpg");

}

Namadoor

Hi,

In your example the second query will delete ALL of the rows that have an imagename of textpic.jpg, not just one like I think you want. You also have a MySQL syntax error. Also, the line $id = $row[$cn] should be $id = $row['0'];. I pefer to use mysql_fetch_array to mysql_fetch_row, because I find it less confusing. This is how I would do what I think you want to do:


$res = mysql_query("select ID, imageno from picture where filename = 'testpic.jpg'"); //I am going to select ID also for the database so I can delete just 1 record every time the loop goes around.

$cn = 0;

while ($row=mysql_fetch_array($res))  {
  $id = $row['imageno'];  //With mysql_fetch_array, you refer to the column name.

$idarray[$cn] = $id;
echo "read into array:".$idarray[$cn];
$cn++;

$deleteimage = mysql_query("delete from picture where ID = $row[ID]"); //This will delete only 1 row.  The way you had it would delete every row with a filename of testpic.jpg.  Also, the query in your example won't work as you forgot to put the closing single-quote around textpic.jpg (You put  "delete from picture where filename = 'testpic.jpg" when it should have been "delete from picture where filename = 'testpic.jpg' ")

I hope this helps.

chemoul78

That worked, thank you very much.
My main mistake was the incorrect call of the array ($cn instead of the actual column name).

Again, thank you.

Chemoul