foreign key
am 09.08.2011 20:31:51 von Chris Stinemetz
Okay. I am pretty new to mysql so this may seem like a ridiculous
question to some people.
I am trying to use a LEFT JOIN query, but the results I am finding
unusual. For every record occurrence there is for the query, the
results are duplicated by that amount.
So if there are 3 records from the query results, then the output is 3
times what I expect.. if that makes sense.
From what I have researched so far. I believe I may need to add a
foreign key to build the relations between the two tables.
Based on the query can any tell me the correct way of adding the
foreign key if ,in fact, that is what I need?
I can provide table structures and information if necessary.
The query is:
$posts_sql = "SELECT
store_subject,
store_comments,
store_date,
store_tptest,
store_by,
users.user_id,
users.user_name,
users.first_name,
users.last_name
FROM
stores
LEFT JOIN
users
ON
stores.store_by = users.user_id
WHERE
stores.store_subject = '" . mysql_real_escape_string($_GET['id'])."'
ORDER BY
stores.store_date DESC ";
The query dump is:
SELECT store_subject, store_comments, store_date, store_tptest,
store_by, users.user_id, users.user_name, users.first_name,
users.last_name FROM stores LEFT JOIN users ON stores.store_by =
users.user_id WHERE stores.store_subject = 'Noland Park Plaza 3509 S.
Noland Rd' ORDER BY stores.store_date DESC
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: foreign key
am 09.08.2011 20:44:49 von Peter Lind
On 9 August 2011 20:31, Chris Stinemetz wrote:
> Okay. I am pretty new to mysql so this may seem like a ridiculous
> question to some people.
>
> I am trying to use a LEFT JOIN query, but the results I am finding
> unusual. For every record occurrence there is for the query, the
> results are duplicated by that amount.
>
> So if there are 3 records from the query results, then the output is 3
> times what I expect.. if that makes sense.
>
> From what I have researched so far. I believe I may need to add a
> foreign key to build the relations between the two tables.
>
> Based on the query can any tell me the correct way of adding the
> foreign key if ,in fact, that is what I need?
>
> I can provide table structures and information if necessary.
>
> The query is:
>
> $posts_sql = "SELECT
> store_subject,
> store_comments,
> store_date,
> store_tptest,
> store_by,
> users.user_id,
> users.user_name,
> users.first_name,
> users.last_name
> FROM
> stores
> LEFT JOIN
> users
> ON
> stores.store_by = users.user_id
> WHERE
> stores.store_subject = '" . mysql_real_escape_string($_GET['id'])."'
> ORDER BY
> stores.store_date DESC ";
>
> The query dump is:
>
> SELECT store_subject, store_comments, store_date, store_tptest,
> store_by, users.user_id, users.user_name, users.first_name,
> users.last_name FROM stores LEFT JOIN users ON stores.store_by =
> users.user_id WHERE stores.store_subject = 'Noland Park Plaza 3509 S.
> Noland Rd' ORDER BY stores.store_date DESC
>
Is users.user_id unique?
--
WWW: plphp.dk / plind.dk
LinkedIn: plind
BeWelcome/Couchsurfing: Fake51
Twitter: kafe15
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: foreign key
am 09.08.2011 20:49:34 von Chris Stinemetz
>
> Is users.user_id unique?
>
yes it is.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: foreign key
am 09.08.2011 20:55:29 von Peter Lind
On 9 August 2011 20:49, Chris Stinemetz wrote:
>>
>> Is users.user_id unique?
>>
>
> yes it is.
>
What does your result look like? Hard to say what the problem is
without seeing the result.
--
WWW: plphp.dk / plind.dk
LinkedIn: plind
BeWelcome/Couchsurfing: Fake51
Twitter: kafe15
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: foreign key
am 09.08.2011 21:23:38 von Chris Stinemetz
--bcaec51d2d7a2a9f9904aa178038
Content-Type: text/plain; charset=ISO-8859-1
>>
>
> What does your result look like? Hard to say what the problem is
> without seeing the result.
>
I am echoing the query and printing the get array just for debugging
purposes, but below you can see how it is repeating its' self.
Thank you
Array
(
[id] => Bella Roe 4980 Roe Blvd
)
Bella Roe 4980 Roe BlvdSELECT stores.store_subject, stores.store_comments,
stores.store_date, stores.store_tptest, stores.store_by, users.user_id,
users.user_name, users.first_name, users.last_name FROM stores LEFT JOIN
users ON stores.store_by = users.user_id WHERE stores.store_subject = 'Bella
Roe 4980 Roe Blvd' ORDER BY stores.store_date DESC Chris Stinemetz
08-09-2011 02:08PM600kbps-3.8mbps
testChris Stinemetz
08-09-2011 02:07PM0-250kbps
test1Reply:
Bella Roe 4980 Roe BlvdSELECT stores.store_subject, stores.store_comments,
stores.store_date, stores.store_tptest, stores.store_by, users.user_id,
users.user_name, users.first_name, users.last_name FROM stores LEFT JOIN
users ON stores.store_by = users.user_id WHERE stores.store_subject = 'Bella
Roe 4980 Roe Blvd' ORDER BY stores.store_date DESC Chris Stinemetz
08-09-2011 02:08PM600kbps-3.8mbps
testChris Stinemetz
08-09-2011 02:07PM0-250kbps
test1Reply:
--bcaec51d2d7a2a9f9904aa178038--
Re: foreign key
am 09.08.2011 21:31:33 von Peter Lind
On 9 August 2011 21:23, Chris Stinemetz wrote:
>
>
> >>
> >
> > What does your result look like? Hard to say what the problem is
> > without seeing the result.
> >
>
> I am echoing the query and printing the get array just for debugging purp=
oses, but below you can see how it is repeating its' self.
>
> Thank you
So you're saying that
SELECT stores.store_subject, stores.store_comments, stores.store_date,
stores.store_tptest, stores.store_by, users.user_id, users.user_name,
users.first_name, users.last_name FROM stores LEFT JOIN users ON
stores.store_by =3D users.user_id WHERE stores.store_subject =3D 'Bella
Roe 4980 Roe Blvd' ORDER BY stores.store_date DESC
returns
Chris Stinemetz 08-09-2011 02:08PM 600kbps-3.8mbps test
Chris Stinemetz 08-09-2011 02:08PM 600kbps-3.8mbps test
Chris Stinemetz 08-09-2011 02:07PM 0-250kbps test1
Chris Stinemetz 08-09-2011 02:07PM 0-250kbps test1
From the above, can't see where your problem is but something in your
join is obviously not unique - whether it's the first or second table.
--
WWW: plphp.dk / plind.dk
LinkedIn: plind
BeWelcome/Couchsurfing: Fake51
Twitter: kafe15
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
Re: foreign key
am 09.08.2011 21:48:10 von Chris Stinemetz
--20cf307c9bd6f29bac04aa17d7b3
Content-Type: text/plain; charset=ISO-8859-1
Okay. I just validated it is not in the left join query, but in my php. I
was able to build the correct relations.
Does anyone see any think in the php code below that may cause duplicate
table rows?
Thank you all.
//store.php
include_once 'includes/connect.php';
include_once 'includes/header.php';
print("
".print_r($_GET,true)."
");
$sql = "SELECT store_id, store_subject, store_comments, store_date,
store_tptest, store_by
FROM stores
WHERE store_subject = '" . mysql_real_escape_string($_GET['id'])."'";
$result = mysql_query($sql);
if(!$result)
{
echo 'The latest post could not be displayed, please try again later.';
//echo mysql_error(); //debugging purposes, uncomment when needed
}
else
{
if(mysql_num_rows($result) == 0)
{
echo 'This store visit doesn′t exist.';
}
else
{
while($row = mysql_fetch_assoc($result))
{
//display post data
echo '
' . $row['store_subject'] . ' |
|---|
';
//fetch the posts from the database
$posts_sql = "SELECT
stores.store_subject,
stores.store_comments,
stores.store_date,
stores.store_tptest,
stores.store_by,
users.user_id,
users.user_name,
users.first_name,
users.last_name
FROM
stores
LEFT JOIN
users
ON
stores.store_by = users.user_id
WHERE
stores.store_subject = '" . mysql_real_escape_string($_GET['id'])."'
ORDER BY
stores.store_date DESC ";
//echo $posts_sql;
$posts_result = mysql_query($posts_sql);
if(!$posts_result)
{
echo 'The posts could not be displayed, please try again
later. |
';
//echo mysql_error(); //debugging purposes, uncomment when needed
}
else
{
while($posts_row = mysql_fetch_assoc($posts_result))
{
echo '
' . $posts_row['first_name'] . ' ' .
$posts_row['last_name'] . '
' . date('m-d-Y h:iA',
strtotime($posts_row['store_date'])) . ' |
' . $posts_row['store_tptest'] . '
' .
htmlentities(stripslashes($posts_row['store_comments'])) . ' |
';
}
}
if(!$_SESSION['signed_in'])
{
echo 'You must be
to reply. You can also for an account.';
}
else
{
//show reply box
echo ' |
Reply:
|
';
}
//finish the table
echo '';
}
}
}
include_once 'includes/footer.php';
?>
--20cf307c9bd6f29bac04aa17d7b3--