retrieving 1 row of data from mysql

@NogDogDec 19.2007 — #

[code=php]
 $sql = "SELECT content from bible_verses where display_date='$today'";
 $result = mysql_query($sql);
 $content = mysql_result($result, 0, 0);
 [/code]

@SyCoDec 19.2007 — #Just to clarify the terms you're using, you're returning one column's value from one row, not one row. Here's NogDog's code reduced to one line, on the off chance that makes life easier/neater.

[code=php]$content = mysql_result(mysql_query("SELECT content from bible_verses where display_date='$today'"), 0, 0); [/code]

So out of curosity, are you manually entering the display dates because that sounds like a lot of work to me. I'd be happy to suggest a way to let PHP take the strain if you need it.

@jrthor2authorDec 19.2007 — #what do you mean by "manually entering the display dates"? You mean the dates in the table? If that's what you mean, then yes, manually, but I'd like to not have to :-)

@SyCoDec 19.2007 — #lol, i'll bet ?. That's why I asked. So it's a fair bit of work to set up but the pay off comes every time you enter new verses. You don't even have to do it ?. I'm not going to write the scripts for you but show you the concept.

You'll need to learn 2 two things

cron jobs (on apache) or schedule tasks (on IIS).

CSV uploads

They're not hard and there's a million tutorials already for both of those on Google so I'll leave learning that part to you. To let you basically know what they are (if you don't already) cronjobs are timed events that can trigger a script to run and a CSV is a Comma Separated Values file, one of the "save as" features in most spreadsheet applications (excel,calc etc). CSV's can be uploaded through PHP and processed line by line in a loop. Like I say Google it and it'll be clear, neither are too hard to grasp.

The logic is hard to explain as you need to get to the end to have all the information you need from the start! So read it through before you think you don't get it.

First you need a new colum in your verses table called something like 'displayed', set its default value to 0

This presumes the auto increment id is your ordering column, ie the order they are entered is the order they are displayed.

At midnight every night the cronjob triggers a script that looks for the next verse that hasn't yet been displayed.
select * from versestable where displayed=0 order by id DESC limit 1
That should give the oldest verse stored that has not yet been displayed.

The same script then takes that ID and sets its displayed value to 1, (You can combine the SQL if you want but that's up to you).

So when the first visitor of the day comes to your site, the days verse is chosen. The MySQL select statement is simple
select * from versestable where displayed=1 order by id limit 1
The will get the newest verse with a displayed value of one, it should be the one that had the 'displayed' value flipped at midnight.

That's basically it. The benifit comes when you next enter new verses. You only need to store the verse and any additional info you want eg title, reference etc. You can have anyone, even non techie types, fill out the CSV spreadsheet with the new verses, just put each one on a new line. The data will be stored row by row with the 'displayed' value defaulting to 0. You just have to write an uploading script (see google) and the same non techie types can upload the verses as easily as adding a picture to an email.

So with a cronjob and a CSV upload you can designate the task to anyone, even the person who requested you add it to your site ?

You can still have certain scripts show on special days if you use a nullable displaydate colum and add the dates to the CSV row in the spreadsheet (a separate spreadsheet column too) and leave that field blank if it doesn't matter. Just test for it before updating the 'displayed' flag and also before displaying the verse. This of course complicates things as the SQL statements have to see which one should be displayed and/or have 'displayed' updated. An excellent learning project to extend this with ?

Like I said, no spoon-feeding here, but the payoff is worth the effort in cool stuff learned and future effort saved.

retrieving 1 row of data from mysql

4 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

retrieving 1 row of data from mysql

4 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

4 Comments(s) _↴

Also in #PHP _↴