473,729 Members | 2,064 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Using PHP, But have a MySql Query Problem

Hi,

I am stuck and cannot think my way out of this small problem. I am coding in
PHP but trying to write a MySql query.

I have db that stores vehicles.
I am trying to SELECT vehicles where the date_sold field is less than the
date_sold field + 7 days. The idea is the site still displays vehicles for 7
days after they are sold.

My query is:
SELECT * FROM stock WHERE category = '$type' AND status != 'disabled' AND
date_sold < (date_sold+6048 00) ORDER BY make,model,year ,price

This query seems to have no affect. I still get the same results as though
the "AND date_sold < (date_sold+6048 00)" wasn't there. My result is it
returns all vehicles that != disabled, but that's it.

Thing is the date_sold field is actually empty for vehicles that haven't
been sold. How do I get around this problem.

FYI, I have a field called status. It = either enabled,disable d or sold.
date_sold stores a time stamp value. The day the administrator marks it as
sold, a time stamp is added for that day.
If that helps.

I cannot simply check if status=sold, because like I said, I need the
vehicles to remain displayed for 7 days after being sold.

If anybody knows how the hell to write a query to do the above, you will
become my best friend. (no cable guys reply).

Cheers

YoBro
Jul 17 '05 #1
7 1599
YoBro <yo***@wazzup.c o.nz> wrote:
I am trying to SELECT vehicles where the date_sold field is less than the
date_sold field + 7 days. The idea is the site still displays vehicles for 7
days after they are sold.

My query is:
SELECT * FROM stock WHERE category = '$type' AND status != 'disabled' AND
date_sold < (date_sold+6048 00) ORDER BY make,model,year ,price ^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^

This is always true for any valid date_sold: x<x+1

I guess you are looking for a query returning:
(now()+0)<(date _sold+604800)
Thing is the date_sold field is actually empty for vehicles that haven't
been sold. How do I get around this problem.


OR date_sold IS NULL

--

Daniel Tryba

Jul 17 '05 #2
Hi Daniel,

Thanks for the reply.
Problem is this don't work.

Couple of things:
If i use IS NULL get a mysql error so I have to use date_sold=''
Using (now()+0)<(date _sold+604800) returns me no data.

I am stumped.

YoBro

"Daniel Tryba" <ne************ ****@canopus.nl > wrote in message
news:cg******** **@news.tue.nl. ..
YoBro <yo***@wazzup.c o.nz> wrote:
I am trying to SELECT vehicles where the date_sold field is less than the date_sold field + 7 days. The idea is the site still displays vehicles for 7 days after they are sold.

My query is:
SELECT * FROM stock WHERE category = '$type' AND status != 'disabled' AND date_sold < (date_sold+6048 00) ORDER BY make,model,year ,price

^^^^^^^^^^^^^^^ ^^^^^^^^^^^^^^

This is always true for any valid date_sold: x<x+1

I guess you are looking for a query returning:
(now()+0)<(date _sold+604800)
Thing is the date_sold field is actually empty for vehicles that haven't
been sold. How do I get around this problem.


OR date_sold IS NULL

--

Daniel Tryba

Jul 17 '05 #3
YoBro <yo***@wazzup.c o.nz> wrote:
Couple of things:
If i use IS NULL get a mysql error so I have to use date_sold=''
Using (now()+0)<(date _sold+604800) returns me no data.

I am stumped.


Ehhh, now()+0 returns in the format 200408241155000 00. You'll have to
use the unix_timestamp( ) function if date_sold is a unix timestamp.

--

Daniel Tryba

Jul 17 '05 #4
Hello again,

Really appreciate the help, thanks.

I have changed it to this:
$query="SELECT * FROM stock WHERE category = '$type' AND status !=
'disabled' AND (".time()."+0)> (date_sold+6048 00) ORDER BY
make,model,year ,price";

THis returns only vehicles that are sold. All my in stock vehicles are
missing. This is where I think the problem with the date_sold field having
noting in it for sold vehicles is causing me headaches.

If I add OR date_sold='' it makes no difference.

YoBro

"Daniel Tryba" <ne************ ****@canopus.nl > wrote in message
news:cg******** **@news.tue.nl. ..
YoBro <yo***@wazzup.c o.nz> wrote:
Couple of things:
If i use IS NULL get a mysql error so I have to use date_sold=''
Using (now()+0)<(date _sold+604800) returns me no data.

I am stumped.


Ehhh, now()+0 returns in the format 200408241155000 00. You'll have to
use the unix_timestamp( ) function if date_sold is a unix timestamp.

--

Daniel Tryba

Jul 17 '05 #5
"YoBro" <yo***@wazzup.c o.nz> wrote in message
news:Pn******** ************@ne ws.xtra.co.nz.. .
'disabled' AND (".time()."+0)> (date_sold+6048 00) ORDER BY

^
That operator needs to be changed to the less than operator.

- JP
Jul 17 '05 #6
YoBro wrote:
Hello again,

Really appreciate the help, thanks.

I have changed it to this:
$query="SELECT * FROM stock WHERE category = '$type' AND status !=
'disabled' AND
(time()<(date_s old+604800) or date_sold is null or date_sold = '')
^ ^
ORDER BY make,model,year ,price";

and time() should be the same thing as ".time()."+ 0

note the parethesis around the entire "or" statements.


THis returns only vehicles that are sold. All my in stock vehicles are
missing. This is where I think the problem with the date_sold field having
noting in it for sold vehicles is causing me headaches.

If I add OR date_sold='' it makes no difference.

YoBro

"Daniel Tryba" <ne************ ****@canopus.nl > wrote in message
news:cg******** **@news.tue.nl. ..
YoBro <yo***@wazzup.c o.nz> wrote:
Couple of things:
If i use IS NULL get a mysql error so I have to use date_sold=''
Using (now()+0)<(date _sold+604800) returns me no data.

I am stumped.


Ehhh, now()+0 returns in the format 200408241155000 00. You'll have to
use the unix_timestamp( ) function if date_sold is a unix timestamp.

--

Daniel Tryba


--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)
Jul 17 '05 #7
Hello,

Thanks, that is what I was missing. The brackets around the two parts. It
works perfect now.

YoBro

"Michael Austin" <ma*****@firstd basource.com> wrote in message
news:kr******** **********@news svr22.news.prod igy.com...
YoBro wrote:
Hello again,

Really appreciate the help, thanks.

I have changed it to this:
$query="SELECT * FROM stock WHERE category = '$type' AND status !=
'disabled' AND


(time()<(date_s old+604800) or date_sold is null or date_sold = '')
^ ^
ORDER BY make,model,year ,price";

and time() should be the same thing as ".time()."+ 0

note the parethesis around the entire "or" statements.


THis returns only vehicles that are sold. All my in stock vehicles are
missing. This is where I think the problem with the date_sold field having noting in it for sold vehicles is causing me headaches.

If I add OR date_sold='' it makes no difference.

YoBro

"Daniel Tryba" <ne************ ****@canopus.nl > wrote in message
news:cg******** **@news.tue.nl. ..
YoBro <yo***@wazzup.c o.nz> wrote:

Couple of things:
If i use IS NULL get a mysql error so I have to use date_sold=''
Using (now()+0)<(date _sold+604800) returns me no data.

I am stumped.

Ehhh, now()+0 returns in the format 200408241155000 00. You'll have to
use the unix_timestamp( ) function if date_sold is a unix timestamp.

--

Daniel Tryba


--
Michael Austin.
Consultant - Available.
Donations welcomed. Http://www.firstdbasource.com/donations.html
:)

Jul 17 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
3249
by: Phil Powell | last post by:
http://www.php.net/array_filter I went there at first for my information on filtering mySQL query results using PHP, to no avail. This is more of a Vignette construct (my native environment) so bear with me. I decided the easiest course of action to tackle a perplexing problem involving two queries and having to
2
4016
by: Phil Powell | last post by:
Relevancy scores are normally defined by a MySQL query on a table that has a fulltext index. The rules for relevancy scoring will exclude certain words due to their being too short (minimum default is 4 letters). This is the Fed. Everything is a TLA (three-letter acronym). Therefore, since I'm building a PORTABLE web application, changing MySQL's default settings for fulltext index querying is completely undoable and unrealistic, so...
2
1774
by: cocoalearner | last post by:
I am serving a website using apache. All the php and mysql code I have written works, except for one thing. When I try to create a database named M#2 using the following call, no database is created. $db = 'M#2'; $query = "create database '$db'"; $result = @mysql_query($query); I am able to create a database just fine using this technique when it is named other things (such as M$B, or Global1). I am able to create
0
1723
by: thomas-lists | last post by:
Hey folks, before posting to the bugs-list I would like to try it here to get some help :). Every now and then MySQL-4.0.13 crashes on my Dual-Athlon-MP-machine. Its not reproducible nor I know a query which does it. (The log shows that at the end, so no query :-( : Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 0x5e13a478 is invalid pointer thd->thread_id=2223573
0
1058
by: David McCowan | last post by:
Have a problem with this query, when xBatch is empty or at least seems to be empty. Using DWMX, MySQL, and PHP SELECT donations.id, donations.batch, donations.oikos_id, donations.giftDate, donations.genFund, donations.misFund, donations.benFund, donations.notes, oikos.id, oikos.famLabel FROM donations, oikos WHERE IF(xBatch = '', donations.batch > 0, donations.batch = xBatch) AND
0
12034
by: Ike_strong | last post by:
Can anyone help me to figure out why each time I try to access my database using: c:\mysql\bin\inmysql -root -p and afterwards:(i.e. after inputting my at the password prompt) Enter Password: '******' I get the following:
14
2896
by: Ben | last post by:
I don't know whether anyone can help, but I have an odd problem. I have a PSP (Spyce) script that makes many calls to populate a database. They all work without any problem except for one statement. I first connect to the database... self.con = MySQLdb.connect(user=username, passwd =password) self.cursor = self.con.cursor() self.cursor.execute("SET max_error_count=0")
4
3043
by: yogesh | last post by:
mysql in c++ initialize error occurs a simple program is executed in redhat9.0 , using gcc 3.2.2 compiler version ... #include <stdio.h> #include <mysql.h> #include <string.h> int main() {
221
367587
Atli
by: Atli | last post by:
You may be wondering why you would want to put your files “into” the database, rather than just onto the file-system. Well, most of the time, you wouldn’t. In situations where your PHP application needs to store entire files, the preferred method is to save the file onto the server’s file-system, and store the physical location of the file in your database. This is generally considered to be the easiest and fastest way to store files. ...
2
1804
by: Frank Aune | last post by:
Hi, Explaining this problem is quite tricky, but please bear with me. Ive got an application which uses MySQL (InnoDB) for two things: 1. Store and retrieve application data (including viewing the application log) 2. Store the application log produced by the python logging module The connection and cursor for the two tasks are completely separate, and they
0
9428
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9291
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
9158
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
8160
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6722
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6026
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
4535
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3244
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2171
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.