Page 1 of 1

Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 3:14 pm
by simonmlewis
This is a really weird one:
The page loads with the form.
You select March from the dropdown list.
You click next.

On the following page it checks the database for the word in $month (namely, March) in the database field named 'month', and for 'yes' in the field named 'paid'.

I can tell you that March is not mentioned in the table at all.
The word 'paid' does have 'yes' in it, but not in a field containing 'March'.

Does the SQL need brackets or something, as it appears to be producing an impossible answer.

Code: Select all

<?php
$cookietype = $_COOKIE['type'];
$cookieid = $_COOKIE['userid'];
$month = $_POST['month'];
if ($cookietype == "advertiser" || $cookietype == "admin") 
{
include "dbconn.php";
if ($month != NULL) 
{
$result = mysql_query ("SELECT COUNT(id) AS numrows FROM adverts WHERE month = '$month' AND paid = 'yes'");
if (mysql_num_rows($result)==0) { echo "<b>There are no adverts booked for $month</b>";}
elseif (mysql_num_rows($result)!=0) { echo "<b>There are $numrows adverts booked for $month</b>";}
echo "<br/><br/>If you would like to continue with advertising for this month, click 'Next'.  If not, click 'Restart'.<br/><br/>
<table><tr><td>
<form method='post' action='index.php?page=advertadd1pay&menu=advert&head=create advert'>
<input type='hidden' name='month' value='$month'>
<input type='submit' value='Next'>
</form></td><td>
<form method='post' action='index.php?page=advertadd1&menu=advert&head=create advert'>
<input type='submit' value='Restart'>
</form></td></tr></table>
";
mysql_free_result($result);
}
 
elseif ($month == NULL)
{
  $today = (date("Y-m-d"));
  $month = substr("$today",-5,2);
 echo "<form method='post' action='index.php?page=advertadd1&menu=advert&head=create advert'>
 Please select the month for which you would like your advert shown.<br/><br/>
 <select name='month'>
 <option value='January'>January</option>
 <option value='February'>February</option>
 <option value='March'>March</option>
 <option value='April'>April</option>
 <option value='May'>May</option>
 <option value='June'>June</option>
 <option value='July'>July</option>
 <option value='August'>August</option>
 <option value='September'>September</option>
 <option value='October'>October</option>
 <option value='November'>November</option>
 <option value='December'>December</option>
 </select>
<input type='submit' value='Next'>
</form>";
}
 
$result = mysql_query ("SELECT id FROM adverts WHERE userid = '$cookieid' AND paid = 'yes'");
$num_rows = mysql_num_rows($result); 
echo "<br/><div class='usercommentsform'>You have $num_rows adverts live.</div>";
mysql_free_result($result);
mysql_close($sqlconn);
}
    else 
    {
    echo "<meta http-equiv='Refresh' content='0 ;URL=index.php?page=home&menu=home'>";
    }
    ?>
The answer it gives is:
There are adverts booked for March

If you would like to continue with advertising for this month, click 'Next'. If not, click 'Restart'.

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 3:24 pm
by AbraCadaver
Since you are selecting count(), the the query will always return one row with the count of records that matched, even if it is zero. Pick one var to use, either count() or mysql_num_rows(). The easiest fix would probably be:

Code: Select all

$result = mysql_query ("SELECT id FROM adverts WHERE month = '$month' AND paid = 'yes'");

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 3:27 pm
by simonmlewis
But that's my point - based on that query, it should return NO RECORDS.
What you have done as a quick fix will not give the user the amount of rows that are equal to the query.

ie. they want to know how many records they have got.

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 3:32 pm
by AbraCadaver
simonmlewis wrote:But that's my point - based on that query, it should return NO RECORDS.
What you have done as a quick fix will not give the user the amount of rows that are equal to the query.

ie. they want to know how many records they have got.
And you have done that with mysql_num_rows(). The count() function will always return one row because you asked it to give you a count and the count is 0. You get one row with the count of 0 in it.

So you either need the query I had that actually return rows and then count them with mysql_num_rows(), or use the count() function in the query and then fetch numrows from $result and forget mysql_num_rows() as it will always be one.

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 3:38 pm
by simonmlewis
I'm sorry?
So I am counting the ID field, because it is a primary unique key.
And even though there are NO RESULTS, it gets you a result of '0'???????

How the hell do you do what I want then? ie. count it where there is the month and a 'yes'.
If there is nothing, a (a), but if there is something, then d (b) and show how many rows there are.

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 3:44 pm
by AbraCadaver
I edited my post while you were posting. Using count() you are not asking for the rows, you are asking for a count of the rows to be returned in the result, and that is one row. Read my second paragraph in the previous post. Here are the two ways:

Code: Select all

$result = mysql_query ("SELECT COUNT(id) AS numrows FROM adverts WHERE month = '$month' AND paid = 'yes'");
$numrows = mysql_result($result, 0);
 
//or
 
$result = mysql_query ("SELECT id FROM adverts WHERE month = '$month' AND paid = 'yes'");
$numrows = mysql_num_rows($result);

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 3:48 pm
by simonmlewis
Brilliant - thank you.
I still don't understand why if it finds now results - ie. there is nothing in the database with the criteria given, it has a count at all.

lol

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 3:59 pm
by AbraCadaver
simonmlewis wrote:Brilliant - thank you.
I still don't understand why if it finds now results - ie. there is nothing in the database with the criteria given, it has a count at all.

lol
I'll try an analogy but it may suck :-)

If you ask me to go to the desk and bring you all pieces of paper that are yellow and not folded, I might bring you back 1 or 2 or 100 pieces of paper, or I might bring back no pieces of paper.

If you ask me to go count all pieces of paper that are yellow and not folded, I will bring you back a piece of paper with 1 or 2 or 100 or 0 written on it.

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 4:09 pm
by simonmlewis
No?
If you ask me to go to my cupboard and bring back all the packets of biscuits that are either opened, or chocolate only - and they were all sealed and all digestive only - I'll bring you back nothing whatsoever.

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 4:12 pm
by AbraCadaver
simonmlewis wrote:No?
If you ask me to go to my cupboard and bring back all the packets of biscuits that are either opened, or chocolate only - and they were all sealed and all digestive only - I'll bring you back nothing whatsoever.
But if I ask you to bring me back the count, then you would at least bring me back the number 0, which is not nothing is my point.

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 4:17 pm
by simonmlewis
But that's not logical?
The answer *is* nothing. There are "no results".

The answer is there is nothing there with your request.
Like when you do a search. If it finds no results, you can say "no results" as $result is nothing.

Re: Can you run a query, when a Form field is 'selected'?

Posted: Wed Jan 27, 2010 4:25 pm
by simonmlewis
I guess what you are saying is that using that you could say:

There are $numrows results for $month.

So it will either show:

There are 0 results for January.
or
There are 44 results for January.

... using the same code.

Still doesn't look quite logical, but if you look at it that way, there is some sense.