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
simonmlewis
DevNet Master
Posts: 4435 Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:
Post
by simonmlewis » Fri Jul 17, 2009 5:08 am
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.
onion2k
Jedi Mod
Posts: 5263 Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com
Post
by onion2k » Fri Jul 17, 2009 5:14 am
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:
Post
by simonmlewis » Fri Jul 17, 2009 5:21 am
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.
VladSun
DevNet Master
Posts: 4313 Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria
Post
by VladSun » Fri Jul 17, 2009 5:32 am
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:
Post
by simonmlewis » Fri Jul 17, 2009 12:03 pm
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
Post
by spider.nick » Fri Jul 17, 2009 12:06 pm
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:
Post
by simonmlewis » Fri Jul 17, 2009 12:26 pm
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
Post
by spider.nick » Fri Jul 17, 2009 12:27 pm
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:
Post
by simonmlewis » Fri Jul 17, 2009 1:04 pm
Thanks - what about this part?
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
Post
by spider.nick » Fri Jul 17, 2009 1:20 pm
simonmlewis wrote: Thanks - what about this part?
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:
Post
by simonmlewis » Fri Jul 17, 2009 1:32 pm
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.