Page 1 of 1

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

Posted: Fri Jul 17, 2009 5:08 am
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?

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

Posted: Fri Jul 17, 2009 5:14 am
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?

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

Posted: Fri Jul 17, 2009 5:21 am
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?

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

Posted: Fri Jul 17, 2009 5:32 am
by VladSun
simonmlewis wrote:Can you recommend a better way?
onion2k wrote:Why not use a boolean or an enum?
;)

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

Posted: Fri Jul 17, 2009 12:03 pm
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.

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

Posted: Fri Jul 17, 2009 12:06 pm
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

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

Posted: Fri Jul 17, 2009 12:26 pm
by simonmlewis
Mmmmm thanks., but that's only producing the first result in the table.

Not both, nor adding them up.

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

Posted: Fri Jul 17, 2009 12:27 pm
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

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

Posted: Fri Jul 17, 2009 1:04 pm
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.

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

Posted: Fri Jul 17, 2009 1:20 pm
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

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

Posted: Fri Jul 17, 2009 1:32 pm
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.