Re: php-db foreign key

am 09.08.2011 21:55:23 von Frank Flynn

--Apple-Mail=_6FC9A2E0-A41A-4E51-8DFF-EB928BF8F6A3
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
charset=us-ascii

You already have a foreign key, that is stores.store_by references =
users.user_id. You might not have declared it (which is OK) but if that =
is the key you want that is fine.

I suspect you are seeing an inadvertent Cartesian product. The way you =
have written this query you will get one row from the stores table for =
each row in the users table where a store_by =3D user_id - and because =
you said "LEFT JOIN" you will get one row from the stores table even if =
there are no matching rows in the users table.

So you say you get 3 x the rows you're expecting; are there 3 users that =
match that store_by?

Good Luck,
Frank

On Aug 9, 2011, at 11:31 AM, php-db-digest-help@lists.php.net wrote:

> From: Chris Stinemetz
> Subject: foreign key
> Date: August 9, 2011 11:31:51 AM PDT
> To: php-db@lists.php.net
>=20
>=20
> Okay. I am pretty new to mysql so this may seem like a ridiculous
> question to some people.
>=20
> 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.
>=20
> So if there are 3 records from the query results, then the output is 3
> times what I expect.. if that makes sense.
>=20
> =46rom what I have researched so far. I believe I may need to add a
> foreign key to build the relations between the two tables.
>=20
> Based on the query can any tell me the correct way of adding the
> foreign key if ,in fact, that is what I need?
>=20
> I can provide table structures and information if necessary.
>=20
> The query is:
>=20
> $posts_sql =3D "SELECT
> store_subject,
> store_comments, =09
> store_date,
> store_tptest,=09
> 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 '" . mysql_real_escape_string($_GET['id'])."'
> ORDER BY
> stores.store_date DESC ";
>=20
> The query dump is:
>=20
> 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 =3D
> users.user_id WHERE stores.store_subject =3D 'Noland Park Plaza 3509 =
S.
> Noland Rd' ORDER BY stores.store_date DESC
>=20

--Apple-Mail=_6FC9A2E0-A41A-4E51-8DFF-EB928BF8F6A3--

Re: Re: php-db foreign key

am 09.08.2011 22:25:46 von Chris Stinemetz

>
> Your php code had more than one query running (one inside the other).
> It's the outer query that runs twice, not the inner one returning
> double the results
>

Thanks Peter,

Do you have any suggestions on how to fix this?

Thank you,

Chris

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

Re: Re: php-db foreign key

am 09.08.2011 22:30:40 von Peter Lind

On 9 August 2011 22:25, Chris Stinemetz wrote:
>>
>> Your php code had more than one query running (one inside the other).
>> It's the outer query that runs twice, not the inner one returning
>> double the results
>>
>
>
> Thanks Peter,
>
> Do you have any suggestions on how to fix this?
>
> Thank you,
>
> Chris
>

Yes, debug your code and figure out why it's looping twice instead.
For instance, try the other query in the mysql console.

--

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: Re: php-db foreign key

am 09.08.2011 22:38:20 von Chris Stinemetz

>
> Yes, debug your code and figure out why it's looping twice instead.
> For instance, try the other query in the mysql console.
>

Thank you! It was the first query. I put a LIMIT 1 on it and now it is
working correctly. I appreciate your help!

Thank you,

Chris

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

Re: Re: php-db foreign key

am 09.08.2011 22:41:53 von Peter Lind

On 9 August 2011 22:38, Chris Stinemetz wrote:
>>
>> Yes, debug your code and figure out why it's looping twice instead.
>> For instance, try the other query in the mysql console.
>>
>
> Thank you! It was the first query. I put a LIMIT 1 on it and now it is
> working correctly. I appreciate your help!
>

You're fixing the symptom, not the problem. Your query was returning
multiple values because something is wrong with the query or your
data. If you don't correct it, the problem will likely just grow
bigger.

--

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: Re: php-db foreign key

am 10.08.2011 12:13:33 von Karl DeSaulniers

On Aug 9, 2011, at 3:41 PM, Peter Lind wrote:

> On 9 August 2011 22:38, Chris Stinemetz
> wrote:
>>>
>>> Yes, debug your code and figure out why it's looping twice instead.
>>> For instance, try the other query in the mysql console.
>>>
>>
>> Thank you! It was the first query. I put a LIMIT 1 on it and now it
>> is
>> working correctly. I appreciate your help!
>>
>
> You're fixing the symptom, not the problem. Your query was returning
> multiple values because something is wrong with the query or your
> data. If you don't correct it, the problem will likely just grow
> bigger.
>
>
> --
>
> 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
>

Hi Chris,
Is this what your looking for?

//store.php
include_once 'includes/connect.php';
include_once 'includes/header.php';

//print("

".print_r($_GET,true)."

"); //Not sure what this is
for, uncomment if needed.

if(!$_SESSION['signed_in']) {//Check to see if signed in first, then
do search, otherwise display and search nothing.
echo('

You must be to
reply. You can also for an account. td>

');
}
else {
$sql = "SELECT * FROM stores WHERE store_subject =
'".mysql_real_escape_string($_GET['id'])."'";
$result = mysql_query($sql);
//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 LIMIT=30";// Set a number of records if you dont want to display
ALL records on one page and want to build in pagination.

if(!$result || mysql_num_rows($result) == 0) {
echo('This store visit doesn′t exist.');
}
else {
echo('');
while($row = mysql_fetch_array($result)) {
//display post data
$posts_result = mysql_query($posts_sql);//This may be the culprit
you had. Do you mean to do the $posts_sql query for ever result in
$sql query?? If not, move out of while loop above. Also, can you get
the same result by just using the $posts_sql query and axe the $sql
query?? They look like you get the same results to me. May be redundant.
echo('');
if(!$posts_result || mysql_num_rows($posts_result) == 0) {
echo ('

'.$row['store_subject'].'
No Post results.

');
} else {
while($posts_row = mysql_fetch_array($posts_result)) {
echo('
'.$posts_row['first_name'].'$nbsp;'.
$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'])). '
');
}
}

//show reply box
echo('

Reply:

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

HTH,

Karl DeSaulniers
Design Drumm
http://designdrumm.com

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