SQL not joining properly?! [Archive]

jarv

12-20-2009, 09:08 PM

I would like to join 2 tables wmb_members and wmb_pics on the UserID

this statement shows all members (not joined)

$query = "SELECT * FROM wmb_members ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

this SQL shows 1 record (the first one)

//$query = "SELECT * FROM wmb_pics INNER JOIN wmb_members ON wmb_pics.UserID=wmb_members.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

this SQL shows 2 records (i think the first 2)

//$query = "SELECT * FROM wmb_members LEFT JOIN wmb_pics ON wmb_members.UserID=wmb_pics.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

Fumigator

12-21-2009, 07:22 AM

What are the values of $offset and $rowsPerPage? And how many rows do you expect to see?

jarv

12-21-2009, 08:32 AM

here is the pagination code

##### PAGINATION ###########################################
// how many rows to show per page
$rowsPerPage = 20;

// by default we show first page
$pageNum = 1;

$self = $_SERVER['PHP_SELF'];

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

// how many rows we have in database
$query = "SELECT COUNT(UserID) AS numrows FROM wmb_members";
$row = mysql_fetch_assoc(mysql_query($query));
$numrows = $row['numrows'];

// how many pages we have when using paging?
$maxPage = ceil($numrows/$rowsPerPage);

// creating 'previous' and 'next' link
// plus 'first page' and 'last page' link

// print 'previous' link only if we're not
// on page one
if ($pageNum > 1)
{
$page = $pageNum - 1;
$prev = "<a href=\"$self?page=$page\">[Prev]</a>";
$first = "<a href=\"$self?page=1\">[First Page]</a>";
}
else
{
$prev = '[Prev]'; // we're on page one, don't enable 'previous' link
$first = '[First Page]'; // nor 'first page' link
}

// print 'next' link only if we're not
// on the last page
if ($pageNum < $maxPage)
{
$page = $pageNum + 1;
$next = " <a href=\"$self?page=$page\">[Next]</a> ";

$last = " <a href=\"$self?page=$maxPage\">[Last Page]</a> ";
}
else
{
$next = '[Next]'; // we're on the last page, don't enable 'next' link
$last = '[Last Page]'; // nor 'last page' link
}

##### / PAGINATION ##############################################

Fumigator

12-21-2009, 03:58 PM

Ok... thanks, I guess...

Anyway, what are the values of $offset and $rowsPerPage when you run that JOIN query? And how many rows do you expect to see? If one or two rows is wrong, what is the right number?

jarv

12-21-2009, 11:33 PM

maybe I should add the INNER JOIN into this code

// how many rows we have in database
$query = "SELECT COUNT(UserID) AS numrows FROM wmb_members";
$row = mysql_fetch_assoc(mysql_query($query));
$numrows = $row['numrows']

jarv

12-27-2009, 11:35 AM

I still haven't managed to do this, any help, much appreciated!

bazz

12-27-2009, 12:18 PM

Well Fumi has asked you a question twice without getting an answer so it is difficult to give help.

bazz

jarv

12-27-2009, 12:31 PM

Ok... thanks, I guess...

Anyway, what are the values of $offset and $rowsPerPage when you run that JOIN query? And how many rows do you expect to see? If one or two rows is wrong, what is the right number?

// how many rows to show per page
$rowsPerPage = 10;

// by default we show first page
$pageNum = 1;

$self = $_SERVER['PHP_SELF'];

// if $_GET['page'] defined, use it as page number
if(isset($_GET['page']))
{
$pageNum = $_GET['page'];
}

// counting the offset
$offset = ($pageNum - 1) * $rowsPerPage;

I am currently seeing 10 rows per page which is right but want to include my tblPics table WHERE UserID = UserID

Old Pedant

12-27-2009, 10:26 PM

So do you want *ALL* pictures associated with a given member or only the first one or only the first two or what???

And if you only want the first one or two (or any number other than "all") how do you make the determination of which one(s) to get??

If you query like this:

$query = "SELECT * FROM wmb_members LEFT JOIN wmb_pics "
. " ON wmb_members.UserID=wmb_pics.UserID "
. " ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage;";

Then you will get all pictures (if any) for each member. So if your LIMIT was 10 $rowsPerPage, and the first user had 10 pictures, then you would only get records for that first user. Period.

Incidentaly, you should not use SELECT *, as you will then get at least one duplicate field (the UserID field) and perhaps others. Try to explicitly SELECT only the fields needed on the page.

Anyway, you have to tell us how many pictures you wish to display, per user, and how to pick it/them out of all available for that user.

jarv

12-28-2009, 06:45 PM

thanks, I want to show all pics and all members there are about 16 members and there are currently 3 pictures for my UserID and 2 pictures for someone else

Old Pedant

12-28-2009, 07:24 PM

Okay, then I *think* this works. Untested:

$query = "SELECT M.*, P.field1, P.field2 FROM
. " ( SELECT UserID, rsDateTaken, otherFieldX, otherFieldY "
. " FROM wmb_members "
. " ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage ) AS M,"
. " LEFT JOIN wmb_pics AS P "
. " ON M.UserID=wmb_pics.UserID "
. " ORDER BY M.rsDateTaken DESC;";

Make sure you list only the fields from the two tables that you actually need on the page.

I assume you realize that this will give you 3 records for your userid (for example) so your PHP code has to only show one set of wmb_members fields while showing all the pictures. Easy PHP coding, just making sure you are aware of it.

jarv

12-28-2009, 07:50 PM

Okay, then I *think* this works. Untested:

$query = "SELECT M.*, P.field1, P.field2 FROM
. " ( SELECT UserID, rsDateTaken, otherFieldX, otherFieldY "
. " FROM wmb_members "
. " ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage ) AS M,"
. " LEFT JOIN wmb_pics AS P "
. " ON M.UserID=wmb_pics.UserID "
. " ORDER BY M.rsDateTaken DESC;";

Make sure you list only the fields from the two tables that you actually need on the page.

I assume you realize that this will give you 3 records for your userid (for example) so your PHP code has to only show one set of wmb_members fields while showing all the pictures. Easy PHP coding, just making sure you are aware of it.

thanks, i'll give it a try, do i have to add in field1 and field2 myself?

Old Pedant

12-28-2009, 07:59 PM

do i have to add in field1 and field2 myself?
I suppose you don't *have* to. You can just use SELECT *.

But you will end up with duplicate fields in the results, and that can cause problems.
Also, it's a lot more efficient to specifically choose only the fields you need. Obviously, if a given page needs all the fields in the records, then that's not a particular consideration. But in my experience that's a relatively rare happenstance.

<shrug>You pays your money and makes your choices.</shrug>

jarv

01-01-2010, 11:56 AM

That didn't work, can anyone else please help here?

bazz

01-01-2010, 06:11 PM

what didn't work. was there an error message or did the query not bring back the results you want? The query can't be too far off what is needed so saying it doesn't work is too vague.

Please post the error message, if any.
Please post the query as it is.
Please explain what you have tried to do with what Old Pedant suggested to try to make it work.

bazz

Old Pedant

01-02-2010, 07:38 PM

So instead of answering Bazz's questions, you start all over. Ah well, life's a pain, then you die.

jarv

01-02-2010, 09:35 PM

thanks for everyone's help but it still isn't working, I use an LEFT JOIN and i only get 2 results back, the 2 members that have pictures?!

I want to bring back all members and the pictures WHERE userID = UserID

Old Pedant

01-02-2010, 10:44 PM

Use echo "DEBUG SQL: " . $sql . "<HR>"; to see *exactly* what the query is that you are using and then copy/past the query here.

You should *NOT* be getting just the two records unless you messed up and put a condition on the right side table into your WHERE clause. That converts the query to an INNER JOIN and then, yes, you'd only see the two member records.

But we have to see *EXACTLY* the query you are using to know.

Old Pedant

01-02-2010, 10:45 PM

And I really don't understand how even a mess up on the left join would get only two records. Even then you should get as many records as there are photos for the two members.

But again, with no DEBUG info from you, we are in the dark.

jarv

01-02-2010, 11:30 PM

ok, I am getting 3 records back!

my code

$query = "SELECT * FROM wmb_members LEFT JOIN wmb_pics ON wmb_pics.UserID=wmb_members.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
echo "DEBUG SQL: " . $query . "<HR>";

result:
DEBUG SQL: SELECT * FROM wmb_members LEFT JOIN wmb_pics ON wmb_pics.UserID=wmb_members.UserID ORDER BY rsDatetaken DESC LIMIT 20, 10

Old Pedant

01-03-2010, 01:19 AM

That's not at all the query I gave you. I would not expect it to work as you requested.

In any case, that query is asking for records numbered 21 through 30 (starting from 1--20 through 29 starting from zero).

How many records do you get if you omit the LIMIT???

jarv

01-03-2010, 09:57 AM

echo "DEBUG SQL: " . $sql . "<HR>";

was the code you gave me, I changed $sql to $query

I got 3 results back!

jarv

01-03-2010, 05:17 PM

please help

Old Pedant

01-03-2010, 08:05 PM

I was talking about the SQL query I gave you in post #11. But now that I look at it, I really did mess it up.

WOW! Sorry. I must have been tired.

But why don't we just stop trying to use PHP, completely, until you get it working???

Use a DB tool--DO NOT USE PHP--and try this query in that tool:

SELECT M.userID, P.*
FROM ( SELECT UserID
FROM wmb_members
ORDER BY UserID
LIMIT 0, 10 ) AS M
LEFT JOIN wmb_pics AS P
ON M.UserID = P.UserID
ORDER BY M.userID;

That should get you 10 members (just the userID fields) and then as many pictures as each member has. If a member *has* pictures, then his/her userid will be repeated once for each picture, of course. If the member has no pictures, then his/her userid will show up only once with no picture info.

But *PLEASE*, before we go to the next step, can you give us a list of the fields in each of the two tables???

Why is a field named "rsDateTaken" (which sounds like the date a photo is taken?) a field in the members table??

jarv

01-04-2010, 08:29 AM

thanks, I don't have a query builder db tool, well I could log into phpMyAdmin but I added your SQL and I got: Parse error: syntax error, unexpected T_VARIABLE in D:\wheresmybike.co.uk\wwwroot\stolen-bikes.php on line 87

jarv

01-04-2010, 07:05 PM

so I am nearly there yeah?

Old Pedant

01-04-2010, 07:15 PM

That is a PHP error, not a MySQL error.

Yes, you should login to phpMyAdmin. Even though it's written in PHP, it won't "get in the way" of finding problems like using your own (incorrect, based on that error message) PHP code will.

jarv

01-04-2010, 09:49 PM

mySQL brought back the following

userID PicID UserID fileName
2 1 2 focus.jpg
2 2 2 focus1.jpg
2 3 2 focus2.jpg
3 NULL NULL NULL
4 NULL NULL NULL
5 NULL NULL NULL
11 NULL NULL NULL
12 NULL NULL NULL
13 NULL NULL NULL
14 NULL NULL NULL
16 NULL NULL NULL
17 NULL NULL NULL

rsDateTaken is related to the date the bicycle was taken(stolen)

Old Pedant

01-04-2010, 11:41 PM

PERFECT!!!

That is *EXACTLY* what you want!

If you look there, you will see that you are getting *TEN* userID values, as you wanted.

And then, for that one userID, you are getting 3 records because there are 3 pictures.

This is JUST what you asked for and just what you need!

So now modify that query, JUST A LITTLE BIT, to get the other info you need from the MEMBERS table.

SELECT M.*, P.picid, P.filename
FROM ( SELECT UserID, all, other, fields, you, want
FROM wmb_members
ORDER BY UserID
LIMIT 0, 10 ) AS M
LEFT JOIN wmb_pics AS P
ON M.UserID = P.UserID
ORDER BY M.userID;

In place of all, other, fields, you, want put in all the field names that you really need in your PHP page. If you really don't know what fields you need, you *CAN* use:

SELECT M.*, P.picid, P.filename
FROM ( SELECT *
FROM wmb_members
ORDER BY UserID
LIMIT 0, 10 ) AS M
LEFT JOIN wmb_pics AS P
ON M.UserID = P.UserID
ORDER BY M.userID;

Not recommended, not as efficient, but realisitically will surely be fine since you are only getting a limited number of records.

PHEW!!!

We did it! That was the hard part.

NOW we figure out what your PHP code needs to look like.

For starters, you will need to put in your variable LIMIT values:

$query = "SELECT M.*, P.picid, P.filename "
. " FROM ( SELECT * "
. " FROM wmb_members "
. " ORDER BY UserID "
. " LIMIT " . $offset . "," . $rowsPerPage . " ) AS M "
. " LEFT JOIN wmb_pics AS P "
. " ON M.UserID = P.UserID "
. " ORDER BY M.userID;";

echo "DEBUG SQL: " . $query . "<HR>";

$result = mysql_query($query) or die("DB ERROR: " . my_sql_error() );
$num_rows = mysql_num_rows($result);

echo "DEBUG: query returned " . $num_rows . " rows<hr>\n";

... this is the harder part now ...
... show us what you do here ...

Old Pedant

01-04-2010, 11:45 PM

One more thing...

If you prefer, you *can* use your rsDateTaken in the ORDER BY.

Just be sure to use it in *both* ORDER BYs.

$query = "SELECT M.*, P.picid, P.filename "
. " FROM ( SELECT * "
. " FROM wmb_members "
. " ORDER BY rsDateTaken DESC, UserID ASC "
. " LIMIT " . $offset . "," . $rowsPerPage . " ) AS M "
. " LEFT JOIN wmb_pics AS P "
. " ON M.UserID = P.UserID "
. " ORDER BY M.rsDateTaken DESC, M.userID ASC, P.picid ASC;";

Adding the P.picid to the order by is optional, but won't hurt and allows you to predict the order that pictures will appear in.

jarv

01-05-2010, 08:39 AM

thankyou so much Old Pendant! I am so glad you have helped!
The next bit is a bit more tricky, I now want to loop through all pictures for that user but as you can see in my code below, there is already a WHILE loop set, (maybe I have to do another loop inside?!)

while ($row = mysql_fetch_assoc($result))
{
$jq_add_div_strings .= "animatedcollapse.addDiv('jason$counter', 'hide=1', 'fade=1,height=180px')\n";
$jq_div_classes_csv .= "'jason$counter',";
// store main data array
$bikearray[$row['UserID']]['UserID'] = $row['UserID'];
$bikearray[$row['UserID']]['rsBikeman'] = $row['rsBikeman'];
$bikearray[$row['UserID']]['rsBikemod'] = $row['rsBikemod'];
$bikearray[$row['UserID']]['rsBikeyear'] = $row['rsBikeyear'];
$bikearray[$row['UserID']]['rsBikecolor'] = $row['rsBikecolor'];
$bikearray[$row['UserID']]['rsDatetaken'] = $row['rsDatetaken'];
$bikearray[$row['UserID']]['rsLocation'] = $row['rsLocation'];
$bikearray[$row['UserID']]['rsOtherinfo'] = $row['rsOtherinfo'];
$bikearray[$row['UserID']]['rsLocpostcode'] = $row['rsLocpostcode'];
$bikearray[$row['UserID']]['rsLocked'] = $row['rsLocked'];
$bikearray[$row['UserID']]['lockType'] = $row['lockType'];
$bikearray[$row['UserID']]['frontSus'] = $row['frontSus'];
$bikearray[$row['UserID']]['rearSus'] = $row['rearSus'];
$bikearray[$row['UserID']]['frontLight'] = $row['frontLight'];
$bikearray[$row['UserID']]['rearLight'] = $row['rearLight'];
$bikearray[$row['UserID']]['bikeBell'] = $row['bikeBell'];
$bikearray[$row['UserID']]['bikeBasket'] = $row['bikeBasket'];
$bikearray[$row['UserID']]['bikeBag'] = $row['bikeBag'];
$bikearray[$row['UserID']]['rsUser'] = $row['rsUser'];
$bikearray[$row['UserID']]['rsEmail'] = $row['rsEmail'];

$counter++;
}
$jq_div_classes_csv = rtrim($jq_div_classes_csv,",");

$bikearray_html = array();
$i = 0;

foreach ($bikearray as $id=>$DETAILS)
{
@$bikearray_html[$i] .= "<div id=\"pubholder\" class=\"wrap\">";
$bikearray_html[$i] .= "<div class=\"entry_header\">";
$bikearray_html[$i] .= "<a href=\"javascript:animatedcollapse.show('jason$i')\" >show more details ></a> ";
$bikearray_html[$i] .= "<a href=\"javascript:animatedcollapse.hide('jason$i')\" >hide ^</a>";
$bikearray_html[$i] .= "<div>".$DETAILS['rsUser']."'s ".$DETAILS['rsBikeman']." ".$DETAILS['rsBikemod']." was taken from ".$DETAILS['rsLocation']." on ".$DETAILS['rsDatetaken']."</div>";
$bikearray_html[$i] .= "<div id=\"jason$i\">\n";
$bikearray_html[$i] .= "";
$bikearray_html[$i] .= "Owners email: <a href=\"mailto:".$DETAILS['rsEmail']."\"> ".$DETAILS['rsEmail']."</a>";
$bikearray_html[$i] .= "Bike year: ".$DETAILS['rsBikeyear']." ";
$bikearray_html[$i] .= "Bike color: ".$DETAILS['rsBikecolor']." ";
$bikearray_html[$i] .= "Locked: ".$DETAILS['rsLocked']." ";
$bikearray_html[$i] .= "Lock type: ".$DETAILS['lockType']." ";

$bikearray_html[$i] .= "additional extras:
 Front suspension: ".$DETAILS['frontSus']."
Rear suspension: ".$DETAILS['rearSus']."
Front Light: ".$DETAILS['frontLight']."
Rear Light: ".$DETAILS['rearLight']."
Bell: ".$DETAILS['bikeBell']."
Basket: ".$DETAILS['bikeBasket']."
Bag: ".$DETAILS['bikeBag']."";
$bikearray_html[$i] .= "Other info: ".$DETAILS['rsOtherinfo']." ";
$bikearray_html[$i] .= "";

$bikearray_html[$i] .= "</div>\n";
$bikearray_html[$i] .= "</div>\n";
$bikearray_html[$i] .= "</div>\n";

$i++;
}

Old Pedant

01-05-2010, 07:26 PM

Hoo boy. Sure didn't expect that code!

I'll do my best.

I am *NOT* a PHP person! Barely literate in PHP.

Stuff in red below is where I don't know the code to use.

Here's what I would do to your top loop:

// remember the UserID of the prior row ... no prior row to start, so blank:
$priorid = "";

while ($row = mysql_fetch_assoc($result))
{
// get the UserID for the current row in the results
$curid = $row['UserID'];
// if not the same as the prior row, then create a new set of data in the bikearray:
if ( $curid != $priorid )
{
// I'm guessing that this stuff goes here:
$jq_add_div_strings .= "animatedcollapse.addDiv('jason$counter', 'hide=1', 'fade=1,height=180px')\n";
$jq_div_classes_csv .= "'jason$counter',";

// on firmer footing here...this should be right:
// store main data array
$bikearray[$curid]['UserID'] = $curid;
$bikearray[$curid]['rsBikeman'] = $row['rsBikeman'];
$bikearray[$curid]['rsBikemod'] = $row['rsBikemod'];
$bikearray[$curid]['rsBikeyear'] = $row['rsBikeyear'];
$bikearray[$curid]['rsBikecolor'] = $row['rsBikecolor'];
$bikearray[$curid]['rsDatetaken'] = $row['rsDatetaken'];
$bikearray[$curid]['rsLocation'] = $row['rsLocation'];
$bikearray[$curid]['rsOtherinfo'] = $row['rsOtherinfo'];
$bikearray[$curid]['rsLocpostcode'] = $row['rsLocpostcode'];
$bikearray[$curid]['rsLocked'] = $row['rsLocked'];
$bikearray[$curid]['lockType'] = $row['lockType'];
$bikearray[$curid]['frontSus'] = $row['frontSus'];
$bikearray[$curid]['rearSus'] = $row['rearSus'];
$bikearray[$curid]['frontLight'] = $row['frontLight'];
$bikearray[$curid]['rearLight'] = $row['rearLight'];
$bikearray[$curid]['bikeBell'] = $row['bikeBell'];
$bikearray[$curid]['bikeBasket'] = $row['bikeBasket'];
$bikearray[$curid]['bikeBag'] = $row['bikeBag'];
$bikearray[$curid]['rsUser'] = $row['rsUser'];
$bikearray[$curid]['rsEmail'] = $row['rsEmail'];

// How do you specify an empty array in PHP??
$bikearray[$curid]['pictures'] = ...an empty array ...
// This is how many pictures there are for current UserID:
$piccount = 0;

$counter++;

// and change priorid for the match test at top of loop
$priorid = $curid;
}
// then regardless if new userid or a repeated userid...
// see if this record has a valid (non null, non blank) filename:
$pic = $row['filename'];
if ( $pic is not null or blank )
{
// yes...a valid filename...so add it to the array
$bikearray[$curid]['pictures'][$piccount] = $pic;
// and count it
++$piccount;
}

}

So now each row in your $bikearray has one new column, 'pictures', that is itself an array. An array of the image filenames.

I'll let you work out how to convert the $bikearray rows into HTML.

Man, is this a really really complex way of generating HTML. Is this from some tool? Surely you woudn't write code like this by hand.

oesxyl

01-05-2010, 07:35 PM

// How do you specify an empty array in PHP??
$bikearray[$curid]['pictures'] = ...an empty array ...

$bikearray[$curid]['pictures'] = array();

if ( $pic is not null or blank )

if(!empty($pic))

best regards

Old Pedant

01-05-2010, 07:39 PM

Thanks. You agree with my logic, Oesxyl??

oesxyl

01-05-2010, 07:51 PM

Thanks. You agree with my logic, Oesxyl??
sorry, I didn't follow the thread, but I guess your logic is correct, :)

best regards

jarv

01-05-2010, 11:01 PM

great progress, thanks, ok so I have now:

$query = "SELECT M.*, P.picid, P.filename "
. " FROM ( SELECT * "
. " FROM wmb_members "
. " ORDER BY rsDateTaken DESC, UserID ASC "
. " LIMIT " . $offset . "," . $rowsPerPage . " ) AS M "
. " LEFT JOIN wmb_pics AS P "
. " ON M.UserID = P.UserID "
. " ORDER BY M.rsDateTaken DESC, M.userID ASC, P.picid ASC;";
//$query = "SELECT wmb_members.*, wmb_pics.* FROM wmb_members INNER JOIN wmb_members ON wmb_members.UserID=wmb_pics.UserID ORDER BY rsDatetaken DESC LIMIT $offset, $rowsPerPage";

$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
//echo "DEBUG SQL: " . $query . "<HR>";
//echo "DEBUG: query returned " . $num_rows . " rows<hr>\n";

// remember the UserID of the prior row ... no prior row to start, so blank:
$priorid = "";

while ($row = mysql_fetch_assoc($result))
{
// get the UserID for the current row in the results
$curid = $row['UserID'];
// if not the same as the prior row, then create a new set of data in the bikearray:
if ( $curid != $priorid )
{
// I'm guessing that this stuff goes here:
$jq_add_div_strings .= "animatedcollapse.addDiv('jason$counter', 'hide=1', 'fade=1,height=180px')\n";
$jq_div_classes_csv .= "'jason$counter',";

// on firmer footing here...this should be right:
// store main data array
$bikearray[$curid]['UserID'] = $curid;
$bikearray[$curid]['rsBikeman'] = $row['rsBikeman'];
$bikearray[$curid]['rsBikemod'] = $row['rsBikemod'];
$bikearray[$curid]['rsBikeyear'] = $row['rsBikeyear'];
$bikearray[$curid]['rsBikecolor'] = $row['rsBikecolor'];
$bikearray[$curid]['rsDatetaken'] = $row['rsDatetaken'];
$bikearray[$curid]['rsLocation'] = $row['rsLocation'];
$bikearray[$curid]['rsOtherinfo'] = $row['rsOtherinfo'];
$bikearray[$curid]['rsLocpostcode'] = $row['rsLocpostcode'];
$bikearray[$curid]['rsLocked'] = $row['rsLocked'];
$bikearray[$curid]['lockType'] = $row['lockType'];
$bikearray[$curid]['frontSus'] = $row['frontSus'];
$bikearray[$curid]['rearSus'] = $row['rearSus'];
$bikearray[$curid]['frontLight'] = $row['frontLight'];
$bikearray[$curid]['rearLight'] = $row['rearLight'];
$bikearray[$curid]['bikeBell'] = $row['bikeBell'];
$bikearray[$curid]['bikeBasket'] = $row['bikeBasket'];
$bikearray[$curid]['bikeBag'] = $row['bikeBag'];
$bikearray[$curid]['rsUser'] = $row['rsUser'];
$bikearray[$curid]['rsEmail'] = $row['rsEmail'];

// How do you specify an empty array in PHP??
$bikearray[$curid]['pictures'] = array();
// This is how many pictures there are for current UserID:
$piccount = 0;

$counter++;

// and change priorid for the match test at top of loop
$priorid = $curid;
}
// then regardless if new userid or a repeated userid...
// see if this record has a valid (non null, non blank) filename:
$pic = $row['filename'];
if(!empty($pic))
{
// yes...a valid filename...so add it to the array
$bikearray[$curid]['pictures'][$piccount] = $pic;
// and count it
++$piccount;
}

}
$jq_div_classes_csv = rtrim($jq_div_classes_csv,",");

$bikearray_html = array();
$i = 0;

foreach ($bikearray as $id=>$DETAILS)
{
@$bikearray_html[$i] .= "<div id=\"pubholder\" class=\"wrap\">";
$bikearray_html[$i] .= "<div class=\"entry_header\">";
$bikearray_html[$i] .= "<a href=\"javascript:animatedcollapse.show('jason$i')\" >show more details ></a> ";
$bikearray_html[$i] .= "<a href=\"javascript:animatedcollapse.hide('jason$i')\" >hide ^</a>";
$bikearray_html[$i] .= "<div>".$DETAILS['rsUser']."'s ".$DETAILS['rsBikeman']." ".$DETAILS['rsBikemod']." was taken from ".$DETAILS['rsLocation']." on ".$DETAILS['rsDatetaken']."</div>";
$bikearray_html[$i] .= "<div id=\"jason$i\">\n";
$bikearray_html[$i] .= "";
$bikearray_html[$i] .= "Owners email: <a href=\"mailto:".$DETAILS['rsEmail']."\"> ".$DETAILS['rsEmail']."</a>";
$bikearray_html[$i] .= "Bike year: ".$DETAILS['rsBikeyear']." ";
$bikearray_html[$i] .= "Bike color: ".$DETAILS['rsBikecolor']." ";
$bikearray_html[$i] .= "Locked: ".$DETAILS['rsLocked']." ";
$bikearray_html[$i] .= "Lock type: ".$DETAILS['lockType']." ";

$bikearray_html[$i] .= "additional extras:
 Front suspension: ".$DETAILS['frontSus']."
Rear suspension: ".$DETAILS['rearSus']."
Front Light: ".$DETAILS['frontLight']."
Rear Light: ".$DETAILS['rearLight']."
Bell: ".$DETAILS['bikeBell']."
Basket: ".$DETAILS['bikeBasket']."
Bag: ".$DETAILS['bikeBag']."";

$bikearray_html[$i] .= "Other info: ".$DETAILS['rsOtherinfo']." ";
$bikearray_html[$i] .= "";

$bikearray_html[$i] .= "</div>\n";
$bikearray_html[$i] .= "</div>\n";
$bikearray_html[$i] .= "</div>\n";

$i++;
}

how do I call the pictures for a user? in the $bikearray_html[$i]?

oesxyl

01-05-2010, 11:21 PM

if(!empty($DETAILS['pictures'])){
foreach($DETAILS['pictures'] as $piccount => $pic){
// do what you want here
}
}else{
// no picture for this user
}

best regards

Old Pedant

01-05-2010, 11:26 PM

LOL! Glad oexsyl answered you. My answer would have been MUCH more hacky.

oesxyl

01-05-2010, 11:31 PM

LOL! Glad oexsyl answered you. My answer would have been MUCH more hacky.
I was thinking hard before, but I realized that you don't like to answers to php questions, :)

best regards

jarv

01-06-2010, 08:28 AM

my code now looks like this:

// remember the UserID of the prior row ... no prior row to start, so blank:
$priorid = "";

while ($row = mysql_fetch_assoc($result))
{
// get the UserID for the current row in the results
$curid = $row['UserID'];
// if not the same as the prior row, then create a new set of data in the bikearray:
if ( $curid != $priorid )
{
// I'm guessing that this stuff goes here:
$jq_add_div_strings .= "animatedcollapse.addDiv('jason$counter', 'hide=1', 'fade=1,height=180px')\n";
$jq_div_classes_csv .= "'jason$counter',";

// on firmer footing here...this should be right:
// store main data array
$bikearray[$curid]['UserID'] = $curid;
$bikearray[$curid]['rsBikeman'] = $row['rsBikeman'];
$bikearray[$curid]['rsBikemod'] = $row['rsBikemod'];
$bikearray[$curid]['rsBikeyear'] = $row['rsBikeyear'];
$bikearray[$curid]['rsBikecolor'] = $row['rsBikecolor'];
$bikearray[$curid]['rsDatetaken'] = $row['rsDatetaken'];
$bikearray[$curid]['rsLocation'] = $row['rsLocation'];
$bikearray[$curid]['rsOtherinfo'] = $row['rsOtherinfo'];
$bikearray[$curid]['rsLocpostcode'] = $row['rsLocpostcode'];
$bikearray[$curid]['rsLocked'] = $row['rsLocked'];
$bikearray[$curid]['lockType'] = $row['lockType'];
$bikearray[$curid]['frontSus'] = $row['frontSus'];
$bikearray[$curid]['rearSus'] = $row['rearSus'];
$bikearray[$curid]['frontLight'] = $row['frontLight'];
$bikearray[$curid]['rearLight'] = $row['rearLight'];
$bikearray[$curid]['bikeBell'] = $row['bikeBell'];
$bikearray[$curid]['bikeBasket'] = $row['bikeBasket'];
$bikearray[$curid]['bikeBag'] = $row['bikeBag'];
$bikearray[$curid]['rsUser'] = $row['rsUser'];
$bikearray[$curid]['rsEmail'] = $row['rsEmail'];

// How do you specify an empty array in PHP??
$bikearray[$curid]['pictures'] = array();
// This is how many pictures there are for current UserID:
$piccount = 0;

$counter++;

// and change priorid for the match test at top of loop
$priorid = $curid;
}
// then regardless if new userid or a repeated userid...
// see if this record has a valid (non null, non blank) filename:
$pic = $row['filename'];
if(!empty($pic))
{
// yes...a valid filename...so add it to the array
$bikearray[$curid]['pictures'][$piccount] = $pic;
// and count it
++$piccount;
}

}
$jq_div_classes_csv = rtrim($jq_div_classes_csv,",");

$bikearray_html = array();
$i = 0;

foreach ($bikearray as $id=>$DETAILS)
{
@$bikearray_html[$i] .= "<div id=\"pubholder\" class=\"wrap\">";
$bikearray_html[$i] .= "<div class=\"entry_header\">";
$bikearray_html[$i] .= "<a href=\"javascript:animatedcollapse.show('jason$i')\" >show more details ></a> ";
$bikearray_html[$i] .= "<a href=\"javascript:animatedcollapse.hide('jason$i')\" >hide ^</a>";
$bikearray_html[$i] .= "<div>".$DETAILS['rsUser']."'s ".$DETAILS['rsBikeman']." ".$DETAILS['rsBikemod']." was taken from ".$DETAILS['rsLocation']." on ".$DETAILS['rsDatetaken']."</div>";
$bikearray_html[$i] .= "<div id=\"jason$i\">\n";
$bikearray_html[$i] .= "";
$bikearray_html[$i] .= "Owners email: <a href=\"mailto:".$DETAILS['rsEmail']."\"> ".$DETAILS['rsEmail']."</a>";
$bikearray_html[$i] .= "Bike year: ".$DETAILS['rsBikeyear']." ";
$bikearray_html[$i] .= "Bike color: ".$DETAILS['rsBikecolor']." ";
$bikearray_html[$i] .= "Locked: ".$DETAILS['rsLocked']." ";
$bikearray_html[$i] .= "Lock type: ".$DETAILS['lockType']." ";

$bikearray_html[$i] .= "additional extras:
 Front suspension: ".$DETAILS['frontSus']."
Rear suspension: ".$DETAILS['rearSus']."
Front Light: ".$DETAILS['frontLight']."
Rear Light: ".$DETAILS['rearLight']."
Bell: ".$DETAILS['bikeBell']."
Basket: ".$DETAILS['bikeBasket']."
Bag: ".$DETAILS['bikeBag']."";
if(!empty($DETAILS['pictures'])){
foreach($DETAILS['pictures'] as $piccount => $pic){
echo "Picture: ".$DETAILS['pictures']." "; // do what you want here
}
}else{
echo "no picture for user"; // no picture for this user
}

and I am getting an error at the top of my screen: no picture for userno picture for userno picture for userno picture for userno picture for userno picture for userno picture for userPicture: Array
Picture: Array
Picture: Array
no picture for userno picture for user

Please help?!

oesxyl

01-06-2010, 10:28 AM

my code now looks like this:

// remember the UserID of the prior row ... no prior row to start, so blank:
$priorid = "";

while ($row = mysql_fetch_assoc($result))
{
// get the UserID for the current row in the results
$curid = $row['UserID'];
// if not the same as the prior row, then create a new set of data in the bikearray:
if ( $curid != $priorid )
{
// I'm guessing that this stuff goes here:
$jq_add_div_strings .= "animatedcollapse.addDiv('jason$counter', 'hide=1', 'fade=1,height=180px')\n";
$jq_div_classes_csv .= "'jason$counter',";

// on firmer footing here...this should be right:
// store main data array
$bikearray[$curid]['UserID'] = $curid;
$bikearray[$curid]['rsBikeman'] = $row['rsBikeman'];
$bikearray[$curid]['rsBikemod'] = $row['rsBikemod'];
$bikearray[$curid]['rsBikeyear'] = $row['rsBikeyear'];
$bikearray[$curid]['rsBikecolor'] = $row['rsBikecolor'];
$bikearray[$curid]['rsDatetaken'] = $row['rsDatetaken'];
$bikearray[$curid]['rsLocation'] = $row['rsLocation'];
$bikearray[$curid]['rsOtherinfo'] = $row['rsOtherinfo'];
$bikearray[$curid]['rsLocpostcode'] = $row['rsLocpostcode'];
$bikearray[$curid]['rsLocked'] = $row['rsLocked'];
$bikearray[$curid]['lockType'] = $row['lockType'];
$bikearray[$curid]['frontSus'] = $row['frontSus'];
$bikearray[$curid]['rearSus'] = $row['rearSus'];
$bikearray[$curid]['frontLight'] = $row['frontLight'];
$bikearray[$curid]['rearLight'] = $row['rearLight'];
$bikearray[$curid]['bikeBell'] = $row['bikeBell'];
$bikearray[$curid]['bikeBasket'] = $row['bikeBasket'];
$bikearray[$curid]['bikeBag'] = $row['bikeBag'];
$bikearray[$curid]['rsUser'] = $row['rsUser'];
$bikearray[$curid]['rsEmail'] = $row['rsEmail'];

// How do you specify an empty array in PHP??
$bikearray[$curid]['pictures'] = array();
// This is how many pictures there are for current UserID:
$piccount = 0;

$counter++;

// and change priorid for the match test at top of loop
$priorid = $curid;
}
// then regardless if new userid or a repeated userid...
// see if this record has a valid (non null, non blank) filename:
$pic = $row['filename'];
if(!empty($pic))
{
// yes...a valid filename...so add it to the array
$bikearray[$curid]['pictures'][$piccount] = $pic;
// and count it
++$piccount;
}

}
$jq_div_classes_csv = rtrim($jq_div_classes_csv,",");

$bikearray_html = array();
$i = 0;

foreach ($bikearray as $id=>$DETAILS)
{
@$bikearray_html[$i] .= "<div id=\"pubholder\" class=\"wrap\">";
$bikearray_html[$i] .= "<div class=\"entry_header\">";
$bikearray_html[$i] .= "<a href=\"javascript:animatedcollapse.show('jason$i')\" >show more details ></a> ";
$bikearray_html[$i] .= "<a href=\"javascript:animatedcollapse.hide('jason$i')\" >hide ^</a>";
$bikearray_html[$i] .= "<div>".$DETAILS['rsUser']."'s ".$DETAILS['rsBikeman']." ".$DETAILS['rsBikemod']." was taken from ".$DETAILS['rsLocation']." on ".$DETAILS['rsDatetaken']."</div>";
$bikearray_html[$i] .= "<div id=\"jason$i\">\n";
$bikearray_html[$i] .= "";
$bikearray_html[$i] .= "Owners email: <a href=\"mailto:".$DETAILS['rsEmail']."\"> ".$DETAILS['rsEmail']."</a>";
$bikearray_html[$i] .= "Bike year: ".$DETAILS['rsBikeyear']." ";
$bikearray_html[$i] .= "Bike color: ".$DETAILS['rsBikecolor']." ";
$bikearray_html[$i] .= "Locked: ".$DETAILS['rsLocked']." ";
$bikearray_html[$i] .= "Lock type: ".$DETAILS['lockType']." ";

$bikearray_html[$i] .= "additional extras:
 Front suspension: ".$DETAILS['frontSus']."
Rear suspension: ".$DETAILS['rearSus']."
Front Light: ".$DETAILS['frontLight']."
Rear Light: ".$DETAILS['rearLight']."
Bell: ".$DETAILS['bikeBell']."
Basket: ".$DETAILS['bikeBasket']."
Bag: ".$DETAILS['bikeBag']."";
if(!empty($DETAILS['pictures'])){
foreach($DETAILS['pictures'] as $piccount => $pic){
echo "Picture: ".$DETAILS['pictures']." "; // do what you want here
}
}else{
echo "no picture for user"; // no picture for this user
}

and I am getting an error at the top of my screen: no picture for userno picture for userno picture for userno picture for userno picture for userno picture for userno picture for userPicture: Array
Picture: Array
Picture: Array
no picture for userno picture for user

Please help?!
why do you use echo? use . (dot ) to concatenate to $bikearray_html[$i].

best regards

jarv

01-06-2010, 04:44 PM

ok great so now I have

if(!empty($DETAILS['pictures'])){
foreach($DETAILS['pictures'] as $piccount => $pic){
$bikearray_html[$i] .= "Picture: ".$DETAILS['pictures']." "; // do what you want here
}
}else{
echo "no picture for user"; // no picture for this user
}

this outputs:
Picture: Array
Picture: Array
Picture: Array for the 3 pictures for the user I selected which has 3 pictures

only problem, it says: Array and not the filename?!

jarv

01-06-2010, 04:50 PM

I've done it! ... well you have done it! it's $pic I was looking for

thanks sooo much to Old Pendant and oesxyl for all your help on this topic, i know it's been a tough one, well done!

Thanks! xx