PHP/MySQL Row ID's & Num_Rows

[Teckie]

Basically, I need a way of getting around a problem I recently found out...

When I don't have a sequential row ID pattern (1,2,3,4), which comes up like (2,5,6), the script will return absolutely nothing.

This is my current PHP code:
[PHP]<?php
$sql_queries[1] = "SELECT id FROM `news`";
$sql_query = mysql_query( $sql_queries[1] ) or die();
$sql_num_rows = mysql_num_rows( $sql_query ) or die();
for ( $i = $sql_num_rows; $i > 0; $i-- )
{
$sql_queries[2] = "SELECT type,date,headline FROM `news` WHERE id = '".$i."'";
$sql_query2 = mysql_query( $sql_queries[2] ) or die();
$sql_array = mysql_fetch_array( $sql_query2 ) or die();
echo "<tr>\n";
echo "<td>".$sql_array['date']."</td>\n";
echo "<td>".$sql_array['headline']."</td>\n";
echo "<td>".$sql_array['type']."</td>\n";
echo "<td align=\"center\"><a href=\"index.php?page=news&act=edit&id=".$i."\"><i mg src=\"images/form_edit.png\" alt=\"Edit\"></a></td>\n";
echo "<td align=\"center\"><a href=\"index.php?submit=true&page=news&act=list&id =".$i."\"><img src=\"images/form_delete.png\" alt=\"Delete\"></a></td>\n";
echo "</tr>\n";
}
?>[/PHP]

So I basically need a workaround for listing a table that will show all "news" rows, even if the IDs weren't in a specific sequence (eg; I deleted a few articles, added and modified some, etc).

[ak1dnar]

For a Small work You have code lots of Thing. ;)
Please Try to change my Coding as suite to Your Requirement.
Here in my Coding I am using a Another Table Named Products.And from that I am going to fetch all the values. And in <TD> i am going to Display product ID again as the out put. Along with that to your edit And delete pages, again the same ID will Pass. Since it is in side the while loop, I can Print it for Any Number of Rows in the table.


[PHP]<?php
require('dbcon.php');
$sql = "SELECT * FROM products"; // change This Line
$result = mysql_query($sql) or die();
while( $row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td>".$row['p_id']."</td>\n";//change the column Names
echo "<td align=\"center\"><a href=\"index.php?page=news&act=edit&id=".$row['p_id']."\"><img src=\"images/form_edit.png\" alt=\"Edit\"></a></td>\n";
echo "<td align=\"center\"><a href=\"index.php?submit=true&page=news&act=list&id =".$row['p_id']."\"><img src=\"images/form_delete.png\" alt=\"Delete\"></a></td>\n";
echo "</tr><br>";
}
?> [/PHP]

[Teckie]

For a Small work You have code lots of Thing. ;)
Please Try to change my Coding as suite to Your Requirement.
Here in my Coding I am using a Another Table Named Products.And from that I am going to fetch all the values. And in <TD> i am going to Display product ID again as the out put. Along with that to your edit And delete pages, again the same ID will Pass. Since it is in side the while loop, I can Print it for Any Number of Rows in the table.


[PHP]<?php
require('dbcon.php');
$sql = "SELECT * FROM products"; // change This Line
$result = mysql_query($sql) or die();
while( $row = mysql_fetch_assoc($result))
{
echo "<tr>";
echo "<td>".$row['p_id']."</td>\n";//change the column Names
echo "<td align=\"center\"><a href=\"index.php?page=news&act=edit&id=".$row['p_id']."\"><img src=\"images/form_edit.png\" alt=\"Edit\"></a></td>\n";
echo "<td align=\"center\"><a href=\"index.php?submit=true&page=news&act=list&id =".$row['p_id']."\"><img src=\"images/form_delete.png\" alt=\"Delete\"></a></td>\n";
echo "</tr><br>";
}
?> [/PHP]

I noticed that in your script you used "while( $row = mysql_fetch_assoc($result))". What exactly does this do? I found this in a few tutorials but none of them seemed to have explained what it serves in the script.

Thanks for your help though, I'll try it out. :)

[ak1dnar]

you are welcome any time. :) Click here to find out the more about this function.

[ronverdonk]

Example:

while (1 == 1) {
  // code
}
 

The 'while' loop executes as long as the condition between the parentheses is true. Since in this example the expression is aways TRUE, the while will run forever.

In your post you have a condition '$row = mysql_fetch_array($result)'. mysql_fetch_array is a function that returns either a row or FALSE (when there are no more rows).

So your statement

$row = mysql_fetch_array($result)

says:
Execute the code within the while loop as long as the result of the condition ($row=...) is true, i.e. as long as there are rows to retrieve. When there are no more rows the while loop will end.

Hope this is clear.

Ronald :cool:

[Teckie]

Example:

while (1 == 1) {
  // code
}
 

The 'while' loop executes as long as the condition between the parentheses is true. Since in this example the expression is aways TRUE, the while will run forever.

In your post you have a condition '$row = mysql_fetch_array($result)'. mysql_fetch_array is a function that returns either a row or FALSE (when there are no more rows).

So your statement

$row = mysql_fetch_array($result)

says:
Execute the code within the while loop as long as the result of the condition ($row=...) is true, i.e. as long as there are rows to retrieve. When there are no more rows the while loop will end.

Hope this is clear.

Ronald :cool:

Ahh, thanks!

Thank you both for your help. The function does work and I've implemented it in all of the areas I needed to, and they all function excellent. This couldn't have been anymore quicker, I'm quite impressed.

Is there a way to reverse this function? Such as "id" 10-1, instead of 1-10?

[ak1dnar]

from SQL script itself you can make it.

Select * from news GROUP BY id ORDER BY id asc

for Ascending order you can use ASC
for Descending order you can use DSC

[ronverdonk]

Ahh, thanks!

Thank you both for your help. The function does work and I've implemented it in all of the areas I needed to, and they all function excellent. This couldn't have been anymore quicker, I'm quite impressed.

Is there a way to reverse this function? Such as "id" 10-1, instead of 1-10?

Yes, when you want to MySQL select only the rows with column 'id' 1-10 in reverse order, do

SELECT * FROM table WHERE id>=1 AND id<=10 ORDER BY id DESC;

Ronald :cool:

[Teckie]

Of course, I was thinking too much on the PHP side, I forgot all about the SQL order by.

Thanks again. :)

[ronverdonk]

You are welcome! See you next time.

Ronald :cool: