[SOLVED] can't figure out a problem reading from database...

[Dragen]

okay.. what's wrong with this code?

<?php
	$sql = "SELECT * FROM booked";
		if ($result = mysql_query($sql)) {
			if (mysql_num_rows($result)) {
			    while ($row = mysql_fetch_assoc($result)) {
					if($caldate == $row['date']){
						echo " style=\"color:#000000; background-color:#808080\"";
					} 
				}
			}else{
				echo ">not found!</td>";
				exit;
			}
		}else{
			echo ">" . mysql_error() . "</td>";
			exit;
		}
?>

every time I run it I receive the "not found!" error, which means it can't find any rows in the table booked. I know for a fact there are 143 entries in that table... I've quadrouple checked the table name and the row name.. but no luck.

I just can't figure it out.

I must be doing something very silly!

[papaface]

dont you mean:

if (mysql_num_rows($result) >0) {

?

[Dragen]

um.. no.

I've basically copied and pasted this mysql query from a fully working one I've got and have used many times without problems.

The only thing I've changed is the database name and what the while statement does..

[Dragen]

if it helps this is what my table looks like:

booked:

date

2007-05-23

2007-05-22

2007-05-21

2007-05-20

2007-05-19

etc

 

It's only a one column table at the moment..

[Anzeo]

Execute your query without the mysql_num_rows statement and check for output.

[Dragen]

that just stops the error showing up...

Other than that it's the same...

[Anzeo]

So it does not fetch any data, am I correct?

 

try mysql_fech_array instead of assoc.

[Dragen]

nah, that's no good either... I just can't figure out what's wrong...

[Dragen]

oh. yeah. It's not fetching the data. really odd though... unless I've done something rong that I haven't realised.

[ataria]

I got a question.

what exactly do these lines do?

 

	if ($result = mysql_query($sql)) {
			if (mysql_num_rows($result)) {

 

[Anzeo]

@ ataria: first clause checks whether your query was succesful or not and the second clause checks if there's any data received to output.

 

@ Dragen: Well I think it's a problem with your query then when it's not selecting any data.

[Dragen]

if ($result = mysql_query($sql)) { //this line checks if it can connect to the database

otherwise it brings up the mysql error and exits.

}else{
echo ">" . mysql_error() . "</td>";
exit;
}

 

The second line:

if (mysql_num_rows($result)) { //this checks if there is anything in the table

If there's nothing in the table it echo this:

}else{
echo ">not found!</td>";
exit;

 

Anzeo.. I've just tried copying and pasting another query that works and using that.. instead I'm now getting this error:

Query failed

SELECT * FROM booked ORDER BY date ASC

Unknown column 'date' in 'order clause'

Here's the code I'm using now.. pretty similar:

<?php
$sql = "SELECT * FROM booked ORDER BY date ASC";
if ($result = mysql_query($sql)) {
if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {
	  echo ">\t\t<td align=\"center\">";
	  echo $row['date'];
	  echo "</td>\n";
	}
}else{
	echo "\t\t<td align=\"center\">no dates found in database</td>\n";
}
}else{
echo "\t\t<td align=\"center\">Query failed<br />\n\t\t$sql<br />\n\t\t".mysql_error()."</td>\n";
}
?>

I don't get why it says unknown column, although if I put ' around the name it doesn't give a mysql error:

$sql = "SELECT * FROM booked ORDER BY 'date' ASC";

instead it brings up the old 'not found' error...

It's really bugging me... I need this to work for a client whose site I've finished and just thought I'd make a minor change... which isn't working

[Anzeo]

Try using SELECT date AS BookDate FROM booked ORDER BY date and see what the output is

 

BTW, you do not have to explicitly add ASC to your query .

[Dragen]

nope. still the not found error...

should I be recieving a

Unknown column 'date' in 'field list'

error when I use:

$sql = "SELECT date BookDate FROM booked ORDER BY date ASC";

I can't think that's right... although if when I use:

$sql = "SELECT 'date' BookDate FROM booked ORDER BY 'date' ASC";

It doesn't give a mysql error.. just the not found one..

 

Yeah I know I don't need to set ASC it's just a habit...

 

I've decided to post my whole code for you all to look at.. perhaps it's something else effecting it??

<?php
require ('database.php');
//this function displays the calendar
function showYear($month, $year){
?>
    <table border="1" cellpadding="0" cellspacing="0" width="100%" id="calendar">
    	<tr>
		<th width="16%" height="48" align="center" valign="bottom"><?php echo $year; ?></th>
		<th align="center" valign="middle">Sunday</th>
		<th align="center" valign="middle">Monday</th>
		<th align="center" valign="middle">Tuesday</th>
		<th align="center" valign="middle">Wednesday</th>
		<th align="center" valign="middle">Thursday</th>
		<th align="center" valign="middle">Friday</th>
		<th align="center" valign="middle">Saturday</th>
	</tr>
<?php
//check if $month is above 0 but below 13. If so it writes out a month, and increments $month by one.
//once it reaches 12 it stops.
while($month > 0 && $month < 13)
{
showMonth($month, $year);
$month++;
}
?>
</table>
<?php
}

//This function writes the months
function showMonth($month, $year)
{

    $date = mktime(12, 0, 0, $month, 1, $year);
    $daysInMonth = date('t', $date);
    // calculate the position of the first day in the calendar (sunday = 1st column, etc)
    $offset = date("w", $date);
    $rows = 1;

	echo "\t\t<tr>\n";
	//displays month
	echo "\t\t\t<td align=\"right\"><strong>". date('F', $date) . "</strong></td>\n";

    for($i = 1; $i <= $offset; $i++)
    {
	echo "\t\t\t<td width=\"12%\"></td>\n";
    }
    for($day = 1; $day <= $daysInMonth; $day++)
    {
        if( ($day + $offset - 1) % 7 == 0 && $day != 1)
        {
	echo "\t\t</tr>\n";
	echo "\t\t<tr>\n";
	echo "\t\t\t<td></td>\n";
            $rows++;
        }
	//checks if date is booked. If so sets it to class="booked"
	echo "\t\t\t<td align=\"center\" valign=\"middle\" width=\"12%\" height=\"48\"";

	//gets data from table to verify date
	$caldate = $year."-".$month."-".$day;

///// this is the part that's not working...
$sql = "SELECT date FROM booked ORDER BY date ASC";
if ($result = mysql_query($sql)) {
if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {
	  echo ">\t\t<td align=\"center\">";
	  echo $row['date'];
	  echo "</td>\n";
	}
}else{
	echo "\t\t<td align=\"center\">no dates found in database</td>\n";
}
}else{
echo "\t\t<td align=\"center\">Query failed<br />\n\t\t$sql<br />\n\t\t".mysql_error()."</td>\n";
}
	echo ">" . $day . "<br />" . $caldate . "</td>\n";
/// this is the end of the mysql statement...
    }
    while( ($day + $offset) <= $rows * 7)
    {
	echo "\t\t\t<td></td>\n";
        $day++;
    }
	echo "\t\t</tr>\n";
}
?>

 

hopefully I can get this sorted..

[Anzeo]

You need to use brackets because date is a reserved name in sql, just like password, time and so on.

 

Try using SELECT booked.date AS ...

[marmite]

Apologies if this is really silly.

 

But in your initial question, Dragen, when you code "if (result=mysql_query)", does that just TEST for data, or does it also RUN $result=mysql_query and set $result?

 

Cos if it doesn't, $result is not defined and you'd get a blank result.

 

If running the IF stmt also runs the query, then I am also stumped. You could echo $result to check?

[Anzeo]

@marmite: the if clause runs the query, otherwise you woudn't receive the "no data" output as it is defined within the if clause.

 

The if clause just checks if your query is valid.

[Dragen]

okay.. still no good

I've got this statement on another file, which is pretty much the same and it runs fine..:

<?php
$td = 0;
$tdmax = 5;
$sql = "SELECT * FROM booked ORDER BY date ASC";
if ($result = mysql_query($sql)) {
if (mysql_num_rows($result)) {
    while ($row = mysql_fetch_assoc($result)) {
		if($td == $tdmax){
			echo "\t</tr>\n\t<tr>";
			$td = 0;
		}
	  echo "\t\t<td align=\"center\">";
	  echo $row['date'];
	  echo "</td>\n";
	$td++;
	}
}else{
	echo "\t\t<td align=\"center\">no dates found in database</td>\n";
}
}else{
echo "\t\t<td align=\"center\">Query failed<br />\n\t\t$sql<br />\n\t\t".mysql_error()."</td>\n";
}
?>

it basically reads from the booked table and prints out all the dates into a table. the

if($td == $tdmax){
echo "\t</tr>\n\t<tr>";
$td = 0;
}

makes it start a new row when a certain amount of columns have been created..

works no problem...

[Anzeo]

Does your query fetch any data?

[Dragen]

the one I've just posted above does, yeah.

My other one doesn't..

 

I have no idea what was going on, but it's now working :-\ I really don't understand what was happening.... I haven't changed anything but it's suddenly decided to work..

Perhaps it wasn't reading from a file properly last night for some reason, or something..

 

Thanks for all the help guys! (hopefully it wont happen again )

[Anzeo]

Let's hope it doesn't change its mind then.

 

If all problems would dissapear after a good night sleep .. .

[Dragen]

hehe yeah.

Thanks again