SQL question

[kpdvx]

I am building a (simple...) webcomic page for a friend. I created a webadmin page, where he sets what text file goes online at a certain date. This is all connected to a MySQL database. For example, online_date would be set to 5902. Meaning, May 9th, 2002. Each entry has a number, $issue_id, and a colum called current. The way the sql is now, whenever it realizes that today that issue should go online, it sets current to 'y'. The next sql call calls the issue who's current is set to 'y'. Ok, this all works fine, IF there is a issue for every day. However, he wants to upload an issue once a week, or 3 times a week, however he feels. So... i need something that will load the last issue uploaded, not online_date = $current_date. Here is the code i have so far. How can i allow it to load the last issue set to go online? By the way, i cant use max_id, because he may upload issues in advance, and that would load them prematurely.

<?
$query = &quot;SELECT issue_id, online_date from comic_issues WHERE online_date = $current_date &quot;;
$result = mysql_query($query);
@extract(mysql_fetch_array($result));

$query = &quot;UPDATE current_online SET issue_id = $issue_id AND online_date = $online_date WHERE colum_id = '1' &quot;;
$result = mysql_query($query);
?>
<!-- ONLINE flag check. Here we find the comic issue entry that is market as
ONLINE. When we find it we take issue_id and assign it to $issue_id.-->


<!-- Here we do a mysql_query, and grab all of the info for the current comic.
We then take all of that info (which is returned to us in variables) and
give them real pathnames, as defined by sitewide.php.-->
<?
$query = &quot;SELECT issue_id FROM current_online WHERE colum_id = '1' &quot;;
$result = mysql_query($query);
@extract(mysql_fetch_array($result));
$arch_id = $issue_id;

$query = &quot;SELECT issue_title, comic_image, first_rant, first_author,
second_rant, second_author
FROM comic_issues WHERE issue_id = $arch_id&quot;;
$result = mysql_query($query);
//Here will assign each returned field to its own $variable
@extract(mysql_fetch_array($result));

I dont really like this code that much, because it updates the database EVERY time the script is accessed, But it was the best thing I could come up with at the time. Thanks a bunch.

 

[KarveR]

if you just wanna get the latest issue out of the database and id is set to auto increment then just do
Select * from yourdata where id=max(id);
this will only return you one set of data and it will always be the most recent version. ***************************************
Party on, dudes!

 

[kpdvx]

i dont want to use max_id because he may upload comics before they are set to be published. Say he uploads a comic today thats set to go online 2 weeks from now. Well, if you grab the max_id on order to grab the issue_id for today's comic, then SQL will return the issue_id for the comic that isnt supposed to go online until 2 more weeks. So instead of showing that last &quot;current&quot; comic, it will instead show a comic that isnt set to go online for 2 more weeks.

 

[KarveR]

Aah good point ....
OK try adding
AND curdate() >= online_date
to the end, this should stop the future stuff from being displayed. ***************************************
Party on, dudes!

 

[kpdvx]

ahha, that sounds like it might work. I think i see what you are getting at, but... could you put that above code into context for me? Thanks.

 

[freewholly]

Why not just add a display_date column to the comic_issues table in the database, reference that in your initial query and forget current_online? And create a unique index on the display_date column so you can't have two comics interfering with each other.

SELECT max(issue_id),
issue_title,
comic_image,
first_rant,
first_author,
second_rant,
second_author
FROM comic_issues
WHERE display_date <= curdate();

Then just assign the date you want the comic to appear. That will save you three database hits and make the code and database simpler.

 

[KarveR]

That certainly saved me writing it , thanks freewholly
***************************************
Party on, dudes!

 

[kpdvx]

what format should display_date and current_date be in? M-D-Y, or what?

Thanks

 

[KarveR]

use theh standard mysql date format e.g 2002-05-10
which would be 10th may 2002 , mySQL can search and select these dates correctly
***************************************
Party on, dudes!

 

[kpdvx]

when i enter that into mysql, i get an error: &quot;mixing of group columns (min(),max()count()...) with no group columns is illegal if there is no group by clause&quot;

have any ideas?

 

[hos2]

SELECT max(issue_id),
   issue_title,
   comic_image,
   first_rant,
   first_author,
   second_rant,
   second_author
FROM comic_issues
WHERE display_date <= curdate() GROUP BY issue_id;

and probably you should use the max function around every field. (in sybase you must) sql doesn't like to choice for itself.

SELECT max(issue_id),
max(issue_title),
   max(comic_image),
   max(first_rant),
   max(first_author),
   max(second_rant),
   max(second_author)
FROM comic_issues
WHERE display_date <= curdate() GROUP BY issue_id;

the max function only delivers one result, don't know if that was the purpose ??

 

[kpdvx]

yes. that was the purpose. Thank you very much. One more quicky, i use this code to set each variable for the query:
@extract(mysql_fetch_array($result));

what would the variables be named now? maxcomic_issue, or what?

 

[hos2]

don't knnow never use is that way. I use it this way
$row = mysql_fetch_array($result);
$author=$row[&quot;first_author&quot;];
$title=$row[&quot;issue_title&quot;];

 

[KarveR]

An alternative if populating a table is using printf

printf(&quot;<tr><td>%s</td><td>%s</td><td>%s</td></tr>&quot;, $myrow[0], $myrow[1], $myrow[2]); ***************************************
Party on, dudes!