Need Opinion on best way to handle a MySQL retrival

[cooldude832]

I have a script that pushes ads into my table,

on that same page I want to have a link to it saying view.php?ID=.$mysqlID;

where $mysqlID is the ID number autokeyed for this entry.  What is the best way to retrive this ensuring I get this exact result.

I do have a field called Creator and since you can only create one ad at a time I figure the best way would be to select WHERE Creator='$creator' AND Creatordate = '$today', but then I need to figure out how to pick the greatest ad.  I'm sure there is logically ways with while functions, but i need a simple solution?

 

My Other Idea which is probably way easier would be how can I easily pull the last entry to the table (they have IDS that are Autokeyed)

and then I can easily get its ID number.  I could just say mysql select * $num rows. I'll try that unless someone gets me a better solution

[trq]

Take a look at mysql_insert_id().

[Barand]

view.php

$id = $_GET['ID'];
$sql = "SELECT * FROM adtable WHERE ID = $id";

 

 

[cooldude832]

to the last message, I do not know ID because it is generated from autokey in mysql table, but i'm trying this.  Unless I get bombarded I think it will work.:

$sql="SELECT * FROM $table";
$result=mysql_query($sql);
// Mysql_num_row is counting table row
$idnum=mysql_num_rows($result);
echo "Ad has been placed </br> <a href='view.php?ID=".$idnum."'>Click Here</a> To view your ad";

 

any one think this is a bad idea?  Auto key starts at 1 so.

[cooldude832]

wow that is one sexy defined function, couldn't ask for better thanks I'll try it

[Barand]

Sorry, misread it. I thought you already a link "view.php?ID=.$mysqlID;"

[trq]

Relying on id's to order anything is frowned upon. That is not there intended purpose.

[cooldude832]

Any opinion on how I can unique recall the data that was just entered?

I figured pulling back its ID would be a simple way.

[cooldude832]

The ID's aren't for ordering at all, just to give each row unquieness.

 

[trq]

Any opinion on how I can unique recall the data that was just entered?

 

I posted a solution to this several posts up.

 

The ID's aren't for ordering at all, just to give each row unquieness.

 

Exactly.

[cooldude832]

I don't want to be considered a "bad" programmer so if you see an issue with my ideas than feel free to tell me what is considered compliance  to standards that aren't standard I try and work my css/html to xhtml compliance on w3 compliance tester and I feel my php should be industry accepted

[trq]

This method you posted....

 

$sql="SELECT * FROM $table";
$result=mysql_query($sql);
// Mysql_num_row is counting table row
$idnum=mysql_num_rows($result);
echo "Ad has been placed </br> <a href='view.php?ID=".$idnum."'>Click Here</a> To view your ad";

 

Would be considered bad practice and unreliable. There is no guarantee the last record inserted will be at (what you consider) the end of the table.

 

My method will never fail to get the last inserted record.

[cooldude832]

unless some how the two insertions get stacked in the queue at the same time and the test for the last key happens after the second one gets done.  Probably will not happen, but there is that 1-10000000 chance it would.

i'm using:

$idnum=mysql_insert_id();
echo "Ad has been placed </br> <a href='view.php?ID=".$idnum."'>Click Here</a> To view your ad";

 

[Barand]

Relying on id's to order anything is frowned upon. That is not there intended purpose.

Thanks for the lesson in db theory.

 

Have you ever come across an instance where, with an autoincremented id, the highest value is not the last one added?

[cooldude832]

The odds of it happening are 0 for any php programer because php/mysql could never handle a query every .0000000001 seconds.  But th erotically if you had the key retriever far down the page it could happen like 1000+ lines.

[Barand]

In your scenario you are talking about creator and date being enough, implying one per day at most. So .0000000001 seconds doesn't really come into the equation.

[cooldude832]

exactly, but if this was on a good size scale there is that potentially it could happen, but even then their data is across so many tables the tables wouldn't see each other and the odds shot up to 10^99 which we in the computer world call impossible

[Barand]

Well, I bow to you in the computer world.

[cooldude832]

don't bow to me i'm new to this

[Barand]

... we in the computer world ...

I was bowing to all of you

[hitman6003]

I think, and I could be wrong, that the mysql_insert_id function gets the id of the last inserted row by a connection, not from the table overall.  Since the server, both mysql and php, keep each connection seperate, even if a thousand other queries happen from other users visiting a page, it shouldn't affect the result of the function.

[Barand]

That's right, it's the last added in the current connection

[cooldude832]

now that is just smart thinking there