JOIN table with with dates and item_id

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
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: JOIN table with with dates and item_id

Post by requinix »

Seems like you should be JOINing in the items table too, and using that to display not just the renewal date but the item - so it's not written in the code like you have now but pulled from the database. What does the table for items (I assume there is one) look like?

Also, you have duplicate keys on the renewal table. A primary key is also an index so the renewal_id and renewal_id_2 indexes are redundant and wasteful.
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: JOIN table with with dates and item_id

Post by requinix »

By the way, please use

Code: Select all

 tags around your SQL code. (Use =php for PHP code.)


Alright. Join in the item table on a matching item_id. Use its description instead of writing "Car MOT Expiry Date" and such in the code.
1. You're also missing a relationship between the users table and any of the other tables (visitors or renewal) so that won't return what you want. Does visitor_id = $id? If not, how does the users table fit into this?
2. Your JOIN conditions for renewal and visitors are not doing anything. They're pointless. That'll create even more problems.
3. Are there multiple renewals for a user/visitor and item? Are you trying to show only the future renewals?

[syntax=sql]SELECT i.description, r.renewal_date
FROM renewal r
JOIN item i ON r.item_id = i.item_id
WHERE r.visitor_id = $id AND r.renewal_date > NOW()
SELECT * is bad: it'll return every single column from every single table used in the query, overwriting duplicate columns as it goes. If you only need two columns (item.description and renewal.renewal_date) then only get those two columns.

Then use a loop to output each description with the renewal date.


ianhaney wrote:not sure where the renewal_id_2 is coming from as that is not in the database table structure?
Sure it is: it's the unique (and automatically-generated) name for that index on renewal_id. MySQL used just the column name for the first one, then stuck a "_2" onto it for the second one.

Code: Select all

KEY `renewal_id` (`renewal_id`),
KEY `renewal_id_2` (`renewal_id`)
Likely when the table was created, it was created with those two keys on it. As in

Code: Select all

CREATE TABLE renewal (
	...
	INDEX (renewal_id), /* already a duplicate of the index created by the primary key */
	INDEX (renewal_id)  /* a second duplicate */
)
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: JOIN table with with dates and item_id

Post by Celauran »

You don't have a table aliased as v. You're selecting from r, joining u and i. No v.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: JOIN table with with dates and item_id

Post by Celauran »

Looks like you're calling mysqli_fetch_array on the query itself rather than on a result set. Also, I'd recommend specifying the table in your join. ON id = u.id is potentially ambiguous.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: JOIN table with with dates and item_id

Post by Celauran »

And it is. When you're selecting columns from multiple tables, you need to specify which table each column comes from.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: JOIN table with with dates and item_id

Post by Celauran »

Code: Select all

JOIN visitors v ON v.visitor_id = u.id
This looks like it's probably backwards. Can't know for certain without having seen the DB schema.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: JOIN table with with dates and item_id

Post by Celauran »

I take it back, then. Carry on.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: JOIN table with with dates and item_id

Post by Celauran »

Have you run the query directly in MySQL?

Code: Select all

if ($result->num_rows == 1)
What if you have more than one result?
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: JOIN table with with dates and item_id

Post by Celauran »

No. If your query isn't returning anything, you need to determine why.
User avatar
Celauran
Moderator
Posts: 6427
Joined: Tue Nov 09, 2010 2:39 pm
Location: Montreal, Canada

Re: JOIN table with with dates and item_id

Post by Celauran »

Yes, it's the query. If you're expecting results and not getting any, check your query.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: JOIN table with with dates and item_id

Post by Christopher »

I think what Celauran is saying is that your query may not be returning the data that you want it to return. A query can return no results because of an error, or because the WHERE conditions are not selecting any rows.
(#10850)
Post Reply