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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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'.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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'");
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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);
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
AbraCadaver
DevNet Master
Posts: 2572
Joined: Mon Feb 24, 2003 10:12 am
Location: The Republic of Texas
Contact:

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

Post 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.
mysql_function(): WARNING: This extension is deprecated as of PHP 5.5.0, and will be removed in the future. Instead, the MySQLi or PDO_MySQLextension should be used. See also MySQL: choosing an API guide and related FAQ for more information.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

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

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply