Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

nisc wwwxxx, wwwxxx0cm, should producers of software-based services, such as atms, be held liable for economic injuries suffered when their systems fail?, wwwxxx0cm, www.webdp.net, Event 9 IIS log failed to write entry, wwwxxx jeffs, Catastrophic failure Unexpected method call sequence. 0x8000ffff (-2147418113)., ksh lock a file, [unixODBC][Driver Manager]Driver's SQLAllocHandle on SQL_HANDLE_DBC failed

Links

XODOX
Impressum

#1: foreign key

Posted on 2011-08-09 20:31:51 by 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

Report this message

#2: Re: foreign key

Posted on 2011-08-09 20:44:49 by Peter Lind

On 9 August 2011 20:31, Chris Stinemetz <chrisstinemetz@gmail.com> 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?

--
<hype>
WWW: plphp.dk / plind.dk
LinkedIn: plind
BeWelcome/Couchsurfing: Fake51
Twitter: kafe15
</hype>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Report this message

#3: Re: foreign key

Posted on 2011-08-09 20:49:34 by 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

Report this message

#4: Re: foreign key

Posted on 2011-08-09 20:55:29 by Peter Lind

On 9 August 2011 20:49, Chris Stinemetz <chrisstinemetz@gmail.com> 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.


--
<hype>
WWW: plphp.dk / plind.dk
LinkedIn: plind
BeWelcome/Couchsurfing: Fake51
Twitter: kafe15
</hype>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Report this message

#5: Re: foreign key

Posted on 2011-08-09 21:23:38 by 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--

Report this message

#6: Re: foreign key

Posted on 2011-08-09 21:31:33 by Peter Lind

On 9 August 2011 21:23, Chris Stinemetz <chrisstinemetz@gmail.com> 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.

--
<hype>
WWW: plphp.dk / plind.dk
LinkedIn: plind
BeWelcome/Couchsurfing: Fake51
Twitter: kafe15
</hype>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Report this message

#7: Re: foreign key

Posted on 2011-08-09 21:48:10 by 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.

<?php
//store.php
include_once 'includes/connect.php';
include_once 'includes/header.php';
print("<pre>".print_r($_GET,true)."</pre>");
$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&prime;t exist.';
}
else
{
while($row = mysql_fetch_assoc($result))
{
//display post data
echo '<table class="table1" border="1">
<tr>
<th colspan="2">' . $row['store_subject'] . '</th>
</tr>';

//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 '<tr><td>The posts could not be displayed, please try again
later.</tr></td></table>';
//echo mysql_error(); //debugging purposes, uncomment when needed
}
else
{

while($posts_row = mysql_fetch_assoc($posts_result))
{
echo '<tr class="topic-post">
<td class="user-post">' . $posts_row['first_name'] . ' ' .
$posts_row['last_name'] . '<br/>' . date('m-d-Y h:iA',
strtotime($posts_row['store_date'])) . '</td>
<td class="post-content">' . $posts_row['store_tptest'] . '<br/>' .
htmlentities(stripslashes($posts_row['store_comments'])) . '</td>
</tr>';
}
}

if(!$_SESSION['signed_in'])
{
echo '<tr><td colspan=2>You must be <a href="signin.php">signed in</a>
to reply. You can also <a href="signup.php">sign up</a> for an account.';
}
else
{
//show reply box
echo '<tr><td colspan="2"><h2>Reply:</h2>

<form method="post" action="reply.php?id=' . $row['store_id'] . '">
<textarea name="reply-content"></textarea>


<input type="submit" value="Submit reply" />
</form></td></tr>';
}

//finish the table
echo '</table>';
}
}
}
include_once 'includes/footer.php';
?>

--20cf307c9bd6f29bac04aa17d7b3--

Report this message