Page 1 of 1
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 4:28 am
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.
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 6:23 am
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 */
)
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 6:45 am
by Celauran
You don't have a table aliased as v. You're selecting from r, joining u and i. No v.
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 7:29 am
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.
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 7:43 am
by Celauran
And it is. When you're selecting columns from multiple tables, you need to specify which table each column comes from.
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 7:45 am
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.
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 7:57 am
by Celauran
I take it back, then. Carry on.
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 8:07 am
by Celauran
Have you run the query directly in MySQL?
What if you have more than one result?
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 8:17 am
by Celauran
No. If your query isn't returning anything, you need to determine why.
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 8:29 am
by Celauran
Yes, it's the query. If you're expecting results and not getting any, check your query.
Re: JOIN table with with dates and item_id
Posted: Mon Jun 29, 2015 8:54 am
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.