Query Question

Reverse Query

[opiston]

Hi everyone,
I am new to masterworld.com. It looks like a good place for web designers / learners.

Here is my question:
I am querying data from a database; however, php naturally query a database table from top to bottom.
I am trying to query from bottom to top. Is it feasible to perform?

This is an example of the table:
Name ID
Alex 12345
John 23456
Andrew 66666

Naturally, the qeury will display
Alex 12345
John 23456
Andrew 66666

But I to display in the reverse order
Andrew 66666
John 23456
Alex 12345

Please give suggestions and guidance
Thanks
Opiston

[jatar_k]

Welcome to WebmasterWorld opiston,

well, you can order queries based on data stored in the table using ORDER BY, there are other options for SELECT all listed here [dev.mysql.com]

in your particular case you could use

ORDER BY ID DESC

[opiston]

Hi jatar_k,
Thanks for your help.
Actually, the data in the table won't have albetical nor numeric order.
I guess I have to create another attribute which indicates when the data is entered into the table.

Thanks again
Opiston

[grandpa]

I guess I have to create another attribute which indicates when the data is entered into the table.

I usually create a field named recid, making it auto-increment and the primary key. Using that, your records will be in relative order according to when they are added to the table. Then you can use ORDER BY recid DESC. Will that help?

CREATE TABLE table_name (recid INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (recid))

[opiston]

Hi Grandpa,
Another helpful reply!
Thanks for your help, Grandpa.

I came up with a solution. Here it is:
$count_query = mysql_query("SELECT count(*) FROM news");
$count_info = mysql_fetch_row($count_query);
$count = $count_info[0];

$result = mysql_query("SELECT * FROM news");

while ($count > 0) {
$count = $count - 1;
$myrow = mysql_fetch_row($result);
}
echo $myrow[0];
echo $myrow[1];

This will post the last data in the table, and this is very inefficient.

CREATE TABLE table_name (recid INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (recid))

Your solution is a much better one, I definitely try that instead of mine. =)

Thanks
Opiston

[mcibor]

This is shorter, more efficient:) code that will do the same:

if the table is such:

CREATE TABLE news (id INT NOT NULL auto_increment, name VARCHAR(50), PRIMARY KEY(id));

then:

$sql = "SELECT id, name FROM news ORDER BY id";//at the end you can add DESC if you want other direction
$query = mysql_query($sql) or die("Error in query: ".mysql_error());

while ($myrow = mysql_fetch_array($query))
{
echo $myrow["id"];//you can still use $myrow[0]
echo $myrow["name"];
}

If you did it the previous way, you would get only last answer. And try to avoid using fetch_row, in case you want to change your table, then you can add fields only behind everything, loosing thus track.
Best regards
Michal Cibor

PS. Count you can get with $count = mysql_num_rows($query);

[opiston]

Hi mcibor,
With jatar_k and Grandpa's help, I've successfully queried the data from bottom to top.

Your suggestion can seem to make my query more efficient. I will give it a try tonight.

Thank you for your suggestions. (I learned something valuable)

Thanks to jatar_k, Grandpa, and mcibor again for helping

Regards
Opiston

[jatar_k]

aw shucks

we try ;)

glad we could help

[opiston]

Hi all,
I just ran into another problem with the following table.

CREATE TABLE table_name (recid INT(11) NOT NULL AUTO_INCREMENT, PRIMARY KEY (recid))

recid auto increments; however, when I delete a row from and add another row to the table, recid jumps over a number.

for example:
recid
1
2
3
4
5

after I delete and add another row:
recid
1
2
3
4
6

This can be a problem to the way implemented my php code.
Is there a way for recid to increment without jumping over a number?

Thanks for helping
Opiston

[mcibor]

auto_increment means that the id in INSERT INTO will never repeat. If I were you I would disregard the problem (you don't use the id, so why to worry about it?).

You can force the id with INSERT INTO table(id, name) VALUES('5', 'John');
However if id=5 already exists, then this query will return an error.

And think moreover what would happen if you deleted the id=1? Then the next id should be 1 and then 2? No! It's better to leave out empty ids.

Michal Cibor

[grandpa]

Indeed, there should be no reason for concern over the skipped numbers (as resulting from deleted rows). The two most common uses for this auto-incrementing recid are to list the rows in the order of their entry into the table (either ASC or DESC) and to ensure that you are deleting the correct row. If you delete a row with phpMyAdmin, you will notice that the row is identified by the recid.

DELETE FROM `sessdet` WHERE CONCAT(`recid`) = 127070 LIMIT 1;

If I attempted to delete that row by another value (in this table it could be `ipaddress`) then I could potentially delete hundreds of rows, not the one I was thinking about.

Your sort will still be accurate in spite of the missing numbers.

[opiston]

Hi mcibor and Grandpa,
I use recid to get the most recent data entered into the table. Here is my code:

$count_query = mysql_query("SELECT recid FROM news");
$count = mysql_num_rows($count_query);

$result = mysql_query("SELECT title, news_text FROM news WHERE recid = $count");
$myrow = mysql_fetch_array($result);

And there is another page which I display the older data in descending order by using ORDER BY recid DESC. In that page, I don't print the newest data entered, so I use this statement:

$result = mysql_query("SELECT title, news_text FROM news WHERE recid!= $count ORDER BY recid DESC");

So if recid jumps over a number, then I won't be able to print the lastest data.

I think I might have to rethink about my implementation. For now, I will just delete the recid and recreate it everytime I delete some data from it.

Thanks for your help
Opiston

[dhardisty]

hi,

a better way to do it is:
$count_query = mysql_query("SELECT recid, title, news_text FROM news order by recid desc limit 1");
$myrow = mysql_fetch_assoc($result);

Then use $myrow['title'] and $myrow['text'] to show the info.

On the other page, you can use:

$result = mysql_query("SELECT title, news_text FROM news WHERE recid!= " . $myrow['recid'] . " ORDER BY recid DESC");

or, if you don't have the $myrow array available, you can use something like

$result = mysql_query("SELECT title, news_text FROM news ORDER BY recid DESC limit 1,500");

The LIMIT option is a bit confusing:
limit 1 -- means get only one record
limit 1,500 -- means get 500 records, starting from row number 1, which is the second record (first record is row 0).

In general, you should never have to mess around with an auto_increment primary key. It's purpose is to create a unique identifier so that no two rows are ever the same.

best,
Dave

[opiston]

Hi dhardisty,

$result = mysql_query("SELECT title, news_text FROM news ORDER BY recid DESC limit 1,500");

The "limit" function is new to me. (learning something again)

Your suggestion seems like a good idea. I will try it out later tonight or tomorrow.

Thanks for everyone's help again.
Regards
Opiston

[mcibor]

It should be:

$sql = "SELECT title, news_text FROM news ORDER BY recid DESC LIMIT 0,1";
$result = mysql_query($sql) or die("Error in sql: ".mysql_error());

or just LIMIT 1;

Best regards
Michal Cibor

[opiston]

Hi everyone,

I think I fixed everything today.
Thanks for ALL of your help on my problem. Your suggestions have been helpful, and your hospitality has made me feel at home.

Best Regards
Opiston