I'm running into a problem selecting information from a database that has been entered today.  Here's what I have so far...that isn't working:

Code:

<?PHP //do your normal mysql setup and connection calls $dbc = mysql_pconnect('localhost','user','pass'); mysql_select_db('weather',$dbc); //now get stuff from a table $sql = "SELECT event, hrs, closed, notes FROM weather WHERE '$today=CURDATE()'"; $dbq = mysql_query($sql,$dbc); //now spit out the table and rows for the table ?> <table width="100%" border="1" cellspacing="1" cellpadding="1"> <tr></tr> <img src="/images/1360am.jpg" width="180" height="82"><strong><img src="/images/realcountry.jpg" width="180" height="82"><img src="/images/mix.JPG" width="180" height="82"><br> <br> RP Broadcasting Weather Related Announcements</strong><br> <tr></tr> <br> <tr>  <td><div align="center"><font size="2"><strong>Event</font></div>  </td>  <td><div align="center"><font size="2"><strong>2 Hours Late?</font></div>  </td>  <td><div align="center"><font size="2"><strong>Closed?</font></div>  </td>  <td><div align="center"><font size="2"><strong>Notes</font></div>  </td>  <td><div align="center">      <?php   while ($row = mysql_fetch_array($dbq)) { echo "<TR>"; echo "<TD><font size='2'><div align='center'>".$row[event]."&nbsp;</font></div></TD>"; echo "<TD><font size='2'><div align='center'>".$row[hrs]."&nbsp;</font></div></TD>"; echo "<TD><font size='2'><div align='center'>".$row[closed]."&nbsp;</font></div></TD>"; echo "<TD><font size='2'><div align='center'>".$row[notes]."&nbsp;</font></div></TD>"; echo "</TR>"; } echo "</font></TABLE>"; ?>    </div></td> </tr>

I'm not getting anything in the html table:

Any ideas what I'm doing wrong?  Thanks.

on 11/29/05 at 23:06:16, bschultz wrote:

WHERE '$today=CURDATE()' I'm not getting anything in the html table: Any ideas what I'm doing wrong?  Thanks.

1. I question the single quotes
2. I question the $

Here's a working use of curdate() in a where:

Code:

drop table if exists places; create table places (        location        text,        diary           date,        pid             int primary key not null auto_increment); insert into places (location, diary) values        ("Linux Course, Melksham","2005-11-30"),        ("MySQL Course, Melksham","2005-12-01"),        ("MySQL Course, Melksham","2005-12-02"),        ("Reroof Conservatory","2005-12-03"),        ("Reroof Conservatory","2005-12-04"),        ("To Newport","2005-12-05"),        ("Passport Office","2005-12-05"),        ("Old Manor Meering","2005-12-05"); select * from places where diary = curdate();

and this gave (today!!) :

Code:

+------------------------+------------+-----+ | location               | diary      | pid | +------------------------+------------+-----+ | Linux Course, Melksham | 2005-11-30 |   1 | +------------------------+------------+-----+ 1 row in set (0.04 sec)

Thanks Graham...I had been working on other parts of this project all day and my brain wasn't wanting to work by the time I did that part of the script.

This is for an online weather cancellation program for our radio announcers.  When a school calls in that they will open late because of the weather, the announcer just types it into the website.  I still need to figure out an "edit" feature that will display all the announcements so far, and allow you to edit them.  That's a bit over my head yet.  I'm sure I'll be back when I get time to work on that part.

Thanks again Graham.

Can you have an if loop and a while loop in one loop?  Here's what I'm trying to accomplish:

Code:

<?php $rows = "mysql_store_result()"; if ($rows < 1) { echo "There Are No Weather Related Announcements Today"; }         else           while ($row = mysql_fetch_array($dbq)) {   echo "<tr>   <td><strong>Event</td> <td><strong>2 Hours Late?</td> <td><strong>Closed?</td> <td><strong>Notes</td> "; echo "<TR>";   echo "<TD>".$row[event]."&nbsp;"; echo "<TD>".$row[hrs]."&nbsp;";   echo "<TD>".$row[closed]."&nbsp;";   echo "<TD>".$row[notes]."&nbsp;";     echo "</TR>";   }   echo "</TABLE>";   ?>

No matter if there are rows in the database or not, it never get's past the "if" and always echo's:

"There Are No Weather Related Announcements Today".

I can't seem to find in the documaentaion if I can put an "if / else" with a "while".

Thanks.

There's no problem putting a while loop within an if conditional, or vice versa.    I think your problem lies in:

Quote:

$rows = "mysql_store_result()"; if ($rows < 1) {

Where you're storing the LITERAL TEXT mysql_store_result() into a variable called $rows and then comparing it to the number 1.  That text WILL always be less than one, thus the results you're reporting.

Not exactly sure I know exactly what your meant to write here, but I know what you have isn't want you wanted!

What I wanted was this:

If no records match the select statement (that is above the code I posted) then echo "There are no weather related announcements."

If there is a match in the "where" select, echo them in the html table.

Brian

Here's the structure I would choose / prefer to use:


$rs = mysql_query("Place your query in here");
$matches = 0;
echo ("<table>");

while ($row = mysql_fetch_assoc($rs)) {
$matches++;
echo ("<tr>".Place your line code here."</tr>");
}

if (! $nmatches) {
echo ("<tr><td>your NO ANNOUNCEMENT message</td></tr>");

echo ("</table>");


It keeps all the data in the table and it's quick and easy.  Also allows you to add a "there were 20 announcements" later if you want to.

Thanks for the help, Graham.  That still isn't working.  The code below displayed both the result AND the line "There are no weather related announcements" if there was a match.  If there wasn't a match, it worked well.

Code:

<?PHP     //do your normal mysql setup and connection calls   $dbc = mysql_pconnect('localhost','user','pass');   mysql_select_db('weather',$dbc);   //now get stuff from a table   $sql = "SELECT event, hrs, closed, notes FROM weather WHERE today=CURDATE()";   //now spit out the table and rows for the table       $rs = mysql_query($sql,$dbc);   $matches = 0; echo ("<table width='100%' border='1'>");         while ($row = mysql_fetch_assoc($rs)) { $matches++; echo "<tr>   <td><strong>Event</td> <td><strong>2 Hours Late?</td> <td><strong>Closed?</td> <td><strong>Notes</td></tr>"; echo "<TR>";   echo "<TD>".$row[event]."&nbsp;</TD>";   echo "<TD>".$row[hrs]."&nbsp;</TD>";   echo "<TD>".$row[closed]."&nbsp;</TD>";   echo "<TD>".$row[notes]."&nbsp;</TD>";   echo "</TR>";   }   if (! $nmatches) { echo ("<tr><td>There are no weather related announcements today</td><TD>&nbsp;</TD><TD>&nbsp;</TD><TD>&nbsp;</TD></tr>"); }   echo "</TABLE>";   ?>

I got it...

this line
Quote:

if (! $nmatches) {

should have been

Quote:

if (! $matches) {

Thanks again, Graham.

Sorry you were a victim of my 5 a.m. typo  

And glad it's working!

Graham

I was wondering what function in PHP the "n" was for...and I couldn't find anything!  Then I thought it may have just been a typo...thanks again for the help.

I do still need to write the code to be able to edit what has already been entered, but I won't get to that this week...but I will be back

OK...I started working on the script to edit data that's already been entered.

Here is what each form field looks like on the edit page

Code:

<input name="event" type="text" id="event" size="50" maxlength="200"  value="<?php echo"$row[event]" ?>" />

When the enter button is pressed, the form action calls edit_db.php.  Here's what that file has

Code:

$DBhost = "localhost"; $DBuser = "user"; $DBpass = "password"; $DBName = "weather"; mysql_connect($DBhost,$DBuser,$DBpass) or die("Unable to connect to database"); @mysql_select_db("$DBName") or die("Unable to select database $DBName"); $usql = "UPDATE weather SET event='$event', hrs='$hrs', closed='$closed', notes='$notes' "; // compiles query $dosql = mysql_query($usql); // executes query  if ($dosql){    echo "<p>Thank You, your entry has been submitted!</p>        <meta http-equiv=Refresh content=1;url='http://www.xxx.com/edit.php'>";  }  else{    echo mysql_errno().": ".mysql_error()."<BR>";  }  mysql_close ();

I've used code similar to this in the past, and it worked, but I was updating a direct variable.  Now, I'm trying to update "$row[event]" and the edit_db.php script is just deleting the contents...not updating them.  I'm sure this is something simple...but I can't seem to find anything.  Can someone please steer me in the right direction?

Thanks you.

Where you say "deleting", I'm taking it that you mean that it's replacing the contents with nothing rather than deleting the row?   What do you have in variables like $event and $hrs - I'm guessing they contain nothing so that's what's being set.  Can you post the extra code that shows how they're set up and / or chek what they've got in them - perhaps by printing their values to the browser as a test.

yes, deleting means replace with nothing...the problem is, there was info in the db before the edit_db.php ran.

when viewing edit.php, the record for "School A" shows up as "2 hours late":

Code:

+----------------------------------------------------------+ | School       | 2 Hours Late | Closed | Notes       | +----------------------------------------------------------+ | "School A"   | Yes          |        | No preschool    | +----------------------------------------------------------+

After updating and changing "school A" to closed (after edit_db.php ran) I should get this:

Code:

+------------------------+------------+-----------------+ | School     | 2 Hours Late | Closed | Notes            | +------------------------+------------+-----+-----------+ | "School A" |              | Yes    | Classes Cancelled| +------------------------+------------+-----------------+

But instead the columns for event, hrs, closed and notes are all replaced with no data, so the script reads as "there are no records".

The variables $event and such are coming from the database...which this edit_db.php script is taking out the data that was in there and being overwritten with "empty" data.

The data that is in the database (before edit_db.php runs) is viewable in the edit.php page in the browser.  I'm printing those out with this code in the html form:

Code:

<input name="event" type="text" id="event" size="50" maxlength="200"  value="<?php echo"$row[event]" ?>" />

I can post the whole code to both pages if you'd like...just let me know.  Thanks, as always, for the help.

I would be REALLY tempted to print out the query that's being run and track it back from there .... I think the query is corruped somehow.   Did the form that was corrected contain the information in a correctky viewable form?   How is your "Register Globals" set.   Forgive me if you've answered some of those before - it's been a long day!

the form (before it is edited) does display correctly prior to the edit_db.php script running.  Globals are off.  I'll print the query when I get home tonight and get back to you.

Brian

Opperator error!  Graham, I was laying in bed last night, and it hit me...GLOBALS!  The script that I had written a year ago to edit the database was written for a machine with globals ON...I changed

Code:

$usql = "UPDATE weather SET event='$event', hrs='$hrs', closed='$closed', notes='$notes' "; // compiles query

to

Code:

$usql = "UPDATE weather SET event='$_POST[event]', hrs='$_POST[hrs]', closed='$_POST[closed]', notes='$_POST[notes]' "; // compiles query

and everything worked just fine.  Thanks for the help.  Goes to show that sometimes one should look at the little things first...and THEN look into the more complicated things.

Thanks again, Graham.

---