Help on mysql query to compare 2 tables

**post** · Nov 4 '07, 02:06 AM

Originally posted by osward

Hi everyone,

I have query(s) that I need to perform which I couldn't figure out, and following is the situation

I have event table (which stores the event details) that has eid, date, time, place field, etc.
I have a member table (which stores who join which event) that has the same field eid as event table but with other member information fields.

When a member join an event, I want to check if he has already joined another event with the same date, and time in the member table. The only reference is the eid that refers back to the event table's content of time and date

The logic is he couldn't be at the different place at the same time

How am I going to construct the qurey or need more than one query to finish my task. I want to return boolean result. If the result is true, I don't allow the member to join that event and give him a warning.

The only common data both tables holds is eid and I couldn't figure out how I could accomplish my task

I could have add date and time field to the member table but I think I still have to pull out the date and time information from the event table (the event in question) and compare if there is an exist record that carries the same date and time. but I wonder if I could do it without alter any table fields.

Thanks in advance

I believe I could help you figure this out, there is only one thing I need from you is a set of example data on what you are trying to accomplished. Anything will do just something I can use as reference to what you want done. Now I think the easiest way to do this would to be date & time comparisons.

**pbmods** · Nov 4 '07, 01:43 PM

Heya, osward.

How do you associate Users with events? Is there a third table that maps Users to events?

**osward** · Nov 5 '07, 01:59 AM

Hi pbmods and post,

Below are tables and fields in question

A typical event rows of data of the event table ( I just listed fields in question) as follows:
[HTML]eid code name date time place
100 BNOV-07-001 FengShu 2007-11-01 18:00-20:00 Beijing
101 BNOV-07-002 Folk Culture 2007-11-01 18:00-20:00 Hong Kong[/HTML]
Actually, there are 2 user tables. One is event_user and the other is event_user_temp . They are almost identical and reason for 2 tables is, when a member register to a event, he needs to confirm the event via a email link within a time period, or his seat to the event would be offer to others.

The event_user
[HTML]jid eid user_id username code name regdate
1 100 125 osward BNOV-07-001 FengShu 2007-10-28 [/HTML]
If I had already registered and confirmed the above event BNOV-07-001 and I want to join BNOV-07-002 that happens to be the same date and time but different place, so I won't be able to be in two places at the same time, right?

This is the query I want to perform to stop this happens.

The only commod field and is unique is the eid. I perform other basic error checks by using this uid field

Hope this gives a clearer picture and task I want to perform and really appreicate help on this

The above is over my head all day and I spent lots of time trying to figure it out. The closest I could come up is using join clause in the query.
The following is from my test script:
[PHP]$eid = "100";
$user_id = "125";
$tbl_cat = "nuke_event_cat ";
$tbl_tmp = "nuke_event_use r_temp";
$tbl_usr = "nuke_event_use r";

$query ="SELECT $tbl_cat.*, $tbl_usr.user_i d $tbl_usr.userna me, $tbl_usr.regdat e FROM $tbl_cat LEFT JOIN $tbl_usr ON $tbl_cat.eid=$t bl_usr.eid WHERE $tbl_usr.user_i d=$user_id";

$result = mysql_query($qu ery) or die(mysql_error ());
while ($row = mysql_fetch_arr ay($result)) {

echo " $row[date] $row[code] $row[username] $row[regdate] ";[/PHP]
Yet, I could only pull information from the 2 tables namely event and user but couldn't figure out further on the WHERE part of the query

Thanks in advance

**post** · Nov 5 '07, 05:25 AM

Alright I got something working here tell me if it's what you wanted to do.
[php]
<?php
link(connect);

$eid = "100";
$user_id = "125";
$tbl_cat = "nuke_event_cat ";
$tbl_tmp = "nuke_event_use r_temp";
$tbl_usr = "nuke_event_use r";

$query = "SELECT $tbl_cat.date, $tbl_usr.code, $tbl_usr.userna me, $tbl_usr.regdat e FROM $tbl_cat LEFT JOIN $tbl_usr ON $tbl_cat.eid = $tbl_usr.eid WHERE $tbl_usr.user_i d ='$user_id'";

$result = mysql_query($qu ery) or die(mysql_error ());
$dataset1 = mysql_fetch_arr ay($result);

$data_array = Array('date' => $dataset1[date], 'code' => $dataset1[code], 'username' => $dataset1[username], 'regdate' => $dataset1[regdate]);

echo(" <pre> Dataset #1 ");

foreach($data_a rray as $rown => $data) {
echo($rown);ech o(": ");echo($data); echo(" ");

}

$query2 = "SELECT * FROM $tbl_cat WHERE '$dataset1[date]' AND eid != $eid";
$result_s = mysql_query($qu ery2);
$dataset2 = mysql_fetch_arr ay($result_s);

$data_array2 = Array('eid' => $dataset2[eid], 'code' => $dataset2[code], 'name' => $dataset2[name], 'date' => $dataset2[date], 'time' => $dataset2[time], 'place' => $dataset2[place]);

echo(" <pre> Dataset #2 ");

foreach($data_a rray2 as $rown => $data) {
echo($rown);ech o(": ");echo($data); echo(" ");

}
if ($dataset1[date] == $dataset2[date]) {
echo(" <pre> Error: Conflicting Date(s)");
echo(" Code: " . $dataset1[code] . " ID: " . $eid . " Date: " . $dataset1['date']);
echo(" C ode: " . $dataset2[code] . " ID: " . $dataset2[eid] . " Date: " . $dataset2['date'] . " Time: " . $dataset2['time'] . " Place: " . $dataset2['place']);
}
?>
<?php
function link ($opt) {
if ($opt == "connect") {
$link = mysql_connect(' localhost', 'login', 'password');
$db_selected = mysql_select_db ('db_name', $link);
}
if ($opt == "die") {
mysql_close();
}
}
?>
[/php]

All you need to do is edit the function link() with your database name & login/password for sql server.

What I made it do is look for the registered event date in the event_user_cat table then display all the information but it won't pull up EID 100 should get information for 101 everything you need. I hope this helps.

**osward** · Nov 5 '07, 10:09 AM

Hi post, thanks for the help

I understand want you try to do with the code and tested the code on my test script.

You had missed to put the field name 'date' in your $query2 that took me quite some time to figure out what's wrong. Because it returns the first record of my event_cat table without any filtering.
That's fine because you'd done more than I expected and I do really appreicate your help. I, myself, always miss typing something that took me hours to figure out.

I think this is what I want to accomplish. I don't need to return the content itself but just number of row in the second query. If more than one than I reject the request.

Thanks again

**osward** · Nov 5 '07, 03:18 PM

Hi post,

I encounter another question that I couldn't figure out

In the first query, it returns the right set of data. However, In the real world, one user might have join more than one event in different date that will still kept in the tbl_user table.
I had made myself 3 enteries in different date and the dataset1 prints out the 2008-03-01 one instead of all 3 sets of data (2 were prior to 2008-03-01, date in 2007). I couldn't find any restriction on the 1st query to limit the search[PHP]$query = "SELECT $tbl_cat.date, $tbl_usr.code, $tbl_usr.userna me, $tbl_usr.regdat e FROM $tbl_cat LEFT JOIN $tbl_usr ON $tbl_cat.eid = $tbl_usr.eid WHERE $tbl_usr.user_i d ='$user_id'";[/PHP]
As the same reason above for the 2nd query. Does it mean the 2nd query only query once and yet it should have return more than one set of data because I made totally 3 enteries at the 2008-03-01.

Maybe my initial logic doesn't stand.

Your input is highly appericated

Thanks in advance

Help on mysql query to compare 2 tables

Help on mysql query to compare 2 tables

Comment

Comment

Comment

Comment

Comment

Comment