dynamically inserting DB data into ajax pagination script

[Guest]

Hey everyone,

   I stumbled across this great pagination script that uses ajax earlier today.  Its a REAL good one but im having problems getting it to work with my database.   The link to it is here if anyone wants to check it out. http://www.dynamicdrive.com/dynamicindex17/ajaxpaginate/index.htm

If you go to the second page it explains how to get data from the DB.  It seems like he is using the ids as page numbers.

I want to display 5 records at a time, each of which gets it info from different tables.

this is the code I have for such, and it works...

$query1 = "SELECT * FROM video WHERE video_username = '$username' ORDER BY video_upload_date DESC;

        mysql_select_db($database_db294582132, $db294582132);

          $result1 = mysql_query($query1, $db294582132) or die(mysql_error());

// extract the first record as an array

    while($row1 = mysql_fetch_assoc($result1)){

          $video_id      = $row1['video_id'];
        $thumb         = $row1['video_t_filename'];
        $video_title   = $row1['video_title'];
          $song_name     = $row1['video_song_name'];
          $song_artist   = $row1['video_song_artist'];
          $song_unknown  = $row1['video_song_unknown'];
        $caption       = $row1['video_caption'];
        $vehicle_id    = $row1['vehicle_id'];
        $video_setup   = $row1['setup_id'];
        $upload_date   = date("m.d.'y, g:i a", strtotime($row1['video_upload_date']));
//get vehicle info
        $query2 = "SELECT * FROM user_vehicles WHERE vehicle_id = '$vehicle_id'";
        mysql_select_db($database_db294582132, $db294582132);
          $result2 = mysql_query($query2, $db294582132) or die(mysql_error());
        $row2    = mysql_fetch_assoc($result2);
        $year        = $row2['vehicle_year'];
        $make        = $row2['vehicle_make'];
        $model       = $row2['vehicle_model'];
//Get setup info   
        $query4 = "SELECT * FROM vehicle_setups WHERE setup_id = '$video_setup'";
     mysql_select_db($database_db294582132, $db294582132);
       $result4 = mysql_query($query4, $db294582132) or die(mysql_error());
     $row4 = mysql_fetch_assoc($result4);
         $setup = $row4['setup'];
//provide format for uploaded videos section
         echo
         '<div id="shopVidsDiv"><div id="editButtonDiv"><a href="editVideos.php">edit</a></div>
<div id="vehicleDescriptionDiv"><div id="title"><span class="videoStatHeading">TITLE: </span>',$video_title,'</div><div id="Caption"><span class="videoStatHeading">CAPTION: </span>
',$caption,'</div>
    <div id="song"><span class="videoStatHeading">SONG NAME: </span>',$song_name,'<span class="videoStatHeading"> ARTIST: </span>',$song_artist,'</div><div id="vehicle"><span class="videoStatHeading">VEHICLE: </span>',$year,' ',$make,' ',$model,'</div><div id="setup"><span class="videoStatHeading">SETUP: </span>',$setup,'</div><div id="uploadDate"><span class="videoStatHeading">UPLOADED ON: </span>',$upload_date,'</div></div>
<img src="images/videoThumbs/',$thumb,'.jpg" width="100" height="75" alt="',$caption,'" />
</div>';

I cant seem to get that part inserted correctly into

var bookonflowers={
PUT DYNAMIC CODE HERE,
selectedpage: 0 //set page shown by default (0=1st page)
}

That is where he puts his dynamic code in his example

Any help is appreciated on how to do this, I just need a better explanation and a lil guidance.  thanks

[bregent]

>I want to display 5 records at a time, each of which gets it info from different tables.

I can't really help with the php, but just wondering why you are creating 4 queries/result sets since all of the records appear to be related. Why not just pull it all into a record set?

[Guest]

What do you mean exactly by a record set?  This is the first site i've ever made and ive made it from the ground up so sorry if im not to keen on the terminology... I've heard of them but never knew exactly what they were. is it like a database query?

Last night I was trying to condense the three queries into one using a join but I decided not to b/c the last 2 dont always need to get processed(optional inputs).  But if there is any way to make that code cleaner then by all means... please share.

As for everyone else reading this... any ideas on putting this into my ajax pagination?

[bregent]

>What do you mean exactly by a record set?

A recordset is just the results of a sql query.

>Last night I was trying to condense the three queries

>into one using a join but I decided not to b/c the last 2

>dont always need to get processed(optional inputs).

Yes, you should combine all of the queries as it will make your code much simpler and easier to maintain.Only resort to using procedural languages when you can't accomplish what you need using SQL, or if it greatly improves performance.

Not sure what you mean by optional inputs. Do you mean that there is not always data in some of the related tables?

[Guest]

The last 2 queries are getting optional data out of the DB.  The user is not required to fill out those fields but if they do, I want to get that data.

I have designed it this way following the instruction of the book "head first php and my SQL" by Head First Labs.  It said that you should minimize duplicate data in a table as much as possible to keep them small.  Instead put the data in a separate table and reference it with a key.  Your tables should only describe one specific thing, so thats what I did.  I have them linked with foreign keys and primary keys.

In my case each user can have a vehicle saved for them, maybe 2 or 3.  for each vehicle they have the option of listing a car audio setup for that vehicle.  Now when they change their setup and update it on the site, a new setup entry is made.  That way the old pics/video of their old setup are still correct and all new pics will be correct as well.     So essentially each vehicle can have maybe 5. 10 setups linked to it.   Hence the separate table for video data, vehicle and setup.  make sense?

As for the joins, I was kind of confused on how to do it correctly with my particular queries. And since the 2nd and 3rd queries dont always need to be executed, i thought if i left it as 3 it might save some execution time as only 1 short query needs to be executed.

what is your opinion on the matter?

and what do you mean by procedural language? like if/else?