Why won't my MySQL search work? Are there too many queries?

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:

Why won't my MySQL search work? Are there too many queries?

Post by simonmlewis »

Code: Select all

    $query   = "SELECT COUNT(id) AS numrows FROM dxvehicles WHERE title LIKE '%$search%' OR description LIKE '%$search%' OR id = '$search' AND stockstatus = 'in stock'";
The idea here is to produce page numbers, and it works. But I only want it to pick out the results where the field says "in stock".

If it is "sold", I don't want that row to appear.

It sounds simple, but for some reason this will not work.

Can anyone help please?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: Why won't my MySQL search work? Are there too many queries?

Post by onion2k »

I think you'll need some brackets:

[sql]SELECT COUNT(id) AS numrows FROM dxvehicles WHERE (title LIKE '%$search%' OR description LIKE '%$search%' OR id = '$search') AND stockstatus = 'in stock'[/sql]

Are you really using "in stock" as a flag for the stock status? Sounds a bit weird. Why not use a boolean or an enum?
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why won't my MySQL search work? Are there too many queries?

Post by simonmlewis »

Thanks.

And yes, I am. If it is in stock, then the field says "in stock". When it's sold, it gets marked as "sold".

Can you recommend a better way?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: Why won't my MySQL search work? Are there too many queries?

Post by VladSun »

simonmlewis wrote:Can you recommend a better way?
onion2k wrote:Why not use a boolean or an enum?
;)
There are 10 types of people in this world, those who understand binary and those who don't
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why won't my MySQL search work? Are there too many queries?

Post by simonmlewis »

On a similar subject, I am trying this now:

Code: Select all

$result = mysql_query ("SELECT DISTINCT fee, solddate FROM dxvehicles WHERE userid = '$userid' ORDER BY solddate ASC");
while ($row = mysql_fetch_object($result))
      {
if ($row->solddate >= $datefrom && $row->solddate <= $dateto) {
 
echo "<tr valign='top'>
            <td>$row->solddate</td>
            
            <td>";
$totalfee = $totalfee + $row->fee;
            printf ("£%.2f", $totalfee);
            
            echo "</td></tr>";
            }}
    mysql_free_result($result);
If there are several results for 17/07/2009 for example, I want that date to appear once, and the "totalfee" to appear as a total figure.

ie.
17/07/2009 - 15.00
17/07/2009 - 17.00
to become...
17/07/2009 - 32.00
Is there a way to do this, as I can't quite get it.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
spider.nick
Forum Commoner
Posts: 72
Joined: Wed Jul 15, 2009 12:22 pm
Location: Overland Park, KS

Re: Why won't my MySQL search work? Are there too many queries?

Post by spider.nick »

simonmlewis wrote:On a similar subject, I am trying this now:

Code: Select all

$result = mysql_query ("SELECT DISTINCT fee, solddate FROM dxvehicles WHERE userid = '$userid' ORDER BY solddate ASC");
while ($row = mysql_fetch_object($result))
      {
if ($row->solddate >= $datefrom && $row->solddate <= $dateto) {
 
echo "<tr valign='top'>
            <td>$row->solddate</td>
            
            <td>";
$totalfee = $totalfee + $row->fee;
            printf ("£%.2f", $totalfee);
            
            echo "</td></tr>";
            }}
    mysql_free_result($result);
If there are several results for 17/07/2009 for example, I want that date to appear once, and the "totalfee" to appear as a total figure.

ie.
17/07/2009 - 15.00
17/07/2009 - 17.00
to become...
17/07/2009 - 32.00
Is there a way to do this, as I can't quite get it.
Current:

Code: Select all

$result = mysql_query ("SELECT DISTINCT fee, solddate FROM dxvehicles WHERE userid = '$userid' ORDER BY solddate ASC");
 
Updated:

Code: Select all

$result = mysql_query ("SELECT DISTINCT fee, solddate FROM dxvehicles WHERE userid = '$userid' GROUP BY solddate ORDER BY solddate ASC");
Nick
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why won't my MySQL search work? Are there too many queries?

Post by simonmlewis »

Mmmmm thanks., but that's only producing the first result in the table.

Not both, nor adding them up.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
spider.nick
Forum Commoner
Posts: 72
Joined: Wed Jul 15, 2009 12:22 pm
Location: Overland Park, KS

Re: Why won't my MySQL search work? Are there too many queries?

Post by spider.nick »

simonmlewis wrote:Mmmmm thanks., but that's only producing the first result in the table.

Not both, nor adding them up.
Opps.

My Current:

Code: Select all

$result = mysql_query ("SELECT DISTINCT fee, solddate FROM dxvehicles WHERE userid = '$userid' GROUP BY solddate ORDER BY solddate ASC");

My Updated:

Code: Select all

$result = mysql_query ("SELECT SUM(fee), solddate FROM dxvehicles WHERE userid = '$userid' GROUP BY solddate ORDER BY solddate ASC");
Nick
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: Why won't my MySQL search work? Are there too many queries?

Post by simonmlewis »

Thanks - what about this part?

Code: Select all

printf ("£%.2f", $row->fee);
I see there is now no point in adding it all up as the query can do it, but this doesn't produce anything.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
spider.nick
Forum Commoner
Posts: 72
Joined: Wed Jul 15, 2009 12:22 pm
Location: Overland Park, KS

Re: Why won't my MySQL search work? Are there too many queries?

Post by spider.nick »

simonmlewis wrote:Thanks - what about this part?

Code: Select all

printf ("£%.2f", $row->fee);
I see there is now no point in adding it all up as the query can do it, but this doesn't produce anything.
Well, I guess I should have read your code over a little more.

My Current:

Code: Select all

$result = mysql_query ("SELECT SUM(fee), solddate FROM dxvehicles WHERE userid = '$userid' GROUP BY solddate ORDER BY solddate ASC");

Final Updated:

Code: Select all

$result = mysql_query ("SELECT SUM(fee) AS fee, solddate FROM dxvehicles WHERE userid = '$userid' GROUP BY solddate ORDER BY solddate ASC");
When you use SUM(), you lose the field name, hence the need to use AS.

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

Re: Why won't my MySQL search work? Are there too many queries?

Post by simonmlewis »

Bingo

Code: Select all

$result = mysql_query ("SELECT SUM(fee) AS fee, solddate FROM dxvehicles WHERE userid = '$userid' GROUP BY solddate ORDER BY solddate ASC");
while ($row = mysql_fetch_object($result))
      {
if ($row->solddate >= $datefrom && $row->solddate <= $dateto) {
 
echo "<tr valign='top'>
            <td>$row->solddate</td>
            
            <td>";
            printf ("£%.2f", $row->fee);
            
            echo "</td></tr>";
            }}
    mysql_free_result($result);
This works a treat.

Thank's very much.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply