[SOLVED] Displaying multiple items from database

wrathican · May 10, 2007

hi

what i have is a page that has four sections. what i want to have is when the page opens, in that page i want the lastest two items for each section from a database to be displayed. the items to be displayed are an image, a title and a description. but i also want the description to be shortend to say about 25-30 words or about 100 chars, if thats easier?

so in the end i have 4 sections, each showing the lastest two items for each section.

would the best way to do this be to have a seperate table in the database for each of the sections? or would i be able to do it from one table?

would i have to creat a query for each of the sections?

e.g:

SELECT * FROM table WHERE section=section

this site is a complete begginers heaven. you people are so helpful.

thanks in advanced

mpharo · May 10, 2007

You can do it from one table or from a collection of tables very easily....if you post some code and how you want it to work I would be happy to assist you...

wrathican · May 10, 2007

well i would like it to come from one table as i have already created that table and it would save me some hassle. the following code is the layout for my page. there are 4 headings, HTML, FLASH, 3D and IMAGERY these are also links to the page where you can see the rest of the projects from this section. theres a cell for the pic, title and dexcription.

<table width="650" border="0" cellpadding="0" cellspacing="0">
  <!--DWLayoutTable-->
  <tr>
    <td width="325" height="250" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0">
      <!--DWLayoutTable-->
      <tr>
        <td height="19" colspan="2" valign="top"><a href="?=html">HTML</a></td>
        </tr>
      <tr>
        <td width="118" height="113" valign="top"><!--DWLayoutEmptyCell--> </td>
        <td width="207" valign="top"><!--DWLayoutEmptyCell--> </td>
      </tr>
      
      <tr>
        <td height="118" valign="top"><!--DWLayoutEmptyCell--> </td>
        <td valign="top"><!--DWLayoutEmptyCell--> </td>
      </tr>
    </table></td>
    <td width="325" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0">
      <!--DWLayoutTable-->
      <tr>
        <td height="19" colspan="2" valign="top"><a href="?=flash">FLASH</a></td>
        </tr>
      <tr>
        <td width="118" height="113" valign="top"><!--DWLayoutEmptyCell--> </td>
        <td width="207" valign="top"><!--DWLayoutEmptyCell--> </td>
      </tr>
      <tr>
        <td height="118" valign="top"><!--DWLayoutEmptyCell--> </td>
        <td valign="top"><!--DWLayoutEmptyCell--> </td>
      </tr>      
    </table></td>
  </tr>
  
  <tr>
    <td height="250" valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0">
      <!--DWLayoutTable-->
      <tr>
        <td height="19" colspan="2" valign="top"><a href="?=3d">3D</a></td>
        </tr>
      <tr>
        <td width="113" height="113" valign="top"><!--DWLayoutEmptyCell--> </td>
        <td width="212" valign="top"><!--DWLayoutEmptyCell--> </td>
      </tr>
      <tr>
        <td height="118" valign="top"><!--DWLayoutEmptyCell--> </td>
        <td valign="top"><!--DWLayoutEmptyCell--> </td>
      </tr>
    </table></td>
    <td valign="top"><table width="100%" border="0" cellpadding="0" cellspacing="0">
      <!--DWLayoutTable-->
      <tr></a>
        <td height="19" colspan="2" valign="top"><a href="?=imagery">IMAGERY</td>
        </tr>
      <tr>
        <td width="118" height="113" valign="top"><!--DWLayoutEmptyCell--> </td>
        <td width="207" valign="top"><!--DWLayoutEmptyCell--> </td>
      </tr>
      <tr>
        <td height="118" valign="top"><!--DWLayoutEmptyCell--> </td>
        <td valign="top"><!--DWLayoutEmptyCell--> </td>
      </tr>
      
      
    </table></td>
  </tr>
</table>'

mpharo · May 10, 2007

Can you post your table structure as well? also it would be much easier to see the whole page instead of just a section...

StormTheGates · May 11, 2007

Something like this?

     $query = mysql_query("SELECT image, title, desc FROM table WHERE id>1 LIMIT 2"); 
     while($m = mysql_fetch_row($query)){
          $img = $m[0];
          $title = $m[1];
          $desc = substr('$m[2]', 0, 100);
     }

Then later down echo them into the HTML?

wrathican · May 11, 2007

well my table has 7 columns. proj_id, proj_section, proj_title, proj_date, proj_descrip, proj_imagepath, proj_url

the piece of code i pasted before is the whole layout of my page, it contains the four sections.

$query = mysql_query("SELECT image, title, desc FROM table WHERE id>1 LIMIT 2"); 
     while($m = mysql_fetch_row($query)){
          $img = $m[0];
          $title = $m[1];
          $desc = substr('$m[2]', 0, 100);
     }

wouldnt this only get 2 itmes from my table? in total i need 8, 2 for each section.

each section has the title of, html, flash, 3d, imagery

i was thinking of using 4 different sql queries.

$query1 = mysql_query("SELECT proj_title, proj_decrip, proj_imagepath FROM project WHERE proj_section=html LIMIT 2");

$query2 = mysql_query("SELECT proj_title, proj_decrip, proj_imagepath FROM project WHERE proj_section=flash LIMIT 2");

$query3 = mysql_query("SELECT proj_title, proj_decrip, proj_imagepath FROM project WHERE proj_section=3d LIMIT 2");

$query4 = mysql_query("SELECT proj_title, proj_decrip, proj_imagepath FROM project WHERE proj_section=imagery LIMIT 2");

but i would have to use a while statement for each of the queries in each section wouldnt i?

thanks for the suggestions! keep up the good work!

mpharo · May 11, 2007

You can do it with one query...


$query = mysql_query("SELECT proj_title, proj_decrip, proj_imagepath FROM project GROUP BY proj_title ASC");

echo "<table><tr><td>";

while($m = mysql_fetch_row($query)){

If ($title != $m[proj_title]){
          
echo "<table><tr><td colspan=\"3\">$m[proj_title]</td></tr><tr><td>$m[proj_decrip]</td><td>$m[proj_imagepath]</td></tr>";

}Else{

echo "<tr><td>$m[proj_decrip]</td><td>$m[proj_imagepath]</td></tr>";

}

$title=$m[proj_title];

}

echo "</table></td></tr></table>";

This is just to give you an idea of how you can do it, this code should work in your schema but it may not produce the results you want, give it a try and let me know if/how it works and what you need changed...or if you have any questions...Also this will loop through everything, I am not sure what the content of the fields are in your table, if you can post the result from one row in your table I can edit the code to return only 2 results for each heading...

StormTheGates · May 11, 2007

Adjust your query to include unions. Example:

$query = mysql_query("SELECT h.proj_title AS html_title, h.proj_decrip AS html_descrip, h.proj_imagepath AS html_image, f.proj_title AS flash_title, f.proj_decrip AS flash_descrip, f.proj_imagepath AS flash_image, d.proj_title AS 3d_title, d.proj_decrip AS 3d_descrip, d.proj_imagepath AS 3d_image, i.proj_title AS image_title, i.proj_decrip AS image_descrip, i.proj_imagepath AS image_image FROM html h, flash f, 3d d, imagery i WHERE h.id>1"); 
     while($m = mysql_fetch_row($query)){
          $htmlimg = $m[0];
          $htmltitle = $m[1];
          $htmldesc = substr('$m[2]', 0, 100);
          $flashimg = $m[3];
          $flashtitle = $m[4];
          $flashdesc = substr('$m[5]', 0, 100);
          $3dimg = $m[6];
          $3dtitle = $m[7];
          $3ddesc = substr('$m[8]', 0, 100);
          $imageryimg = $m[9];
          $imagerytitle = $m[10];
          $imagerydesc = substr('$m[11]', 0, 100);
     }

Something like this perhaps?

mpharo · May 11, 2007

That is a nasty query and I would not reccomend doing it that way...using my example above it is simple and straight forward, so if you need to hand this off to someone else or ask someone for help on this in the future also for you own benefit, it is very easy to interpret and go with...

StormTheGates · May 11, 2007

Hes not nasty hes adorable!

*cuddles his query*

I think the problem with yours is that it only gets stuff from one table, when apparently there are 4 tables.

mpharo · May 11, 2007

i was thinking of using 4 different sql queries.

$query1 = mysql_query("SELECT proj_title, proj_decrip, proj_imagepath FROM project WHERE proj_section=html LIMIT 2");

$query2 = mysql_query("SELECT proj_title, proj_decrip, proj_imagepath FROM project WHERE proj_section=flash LIMIT 2");

$query3 = mysql_query("SELECT proj_title, proj_decrip, proj_imagepath FROM project WHERE proj_section=3d LIMIT 2");

$query4 = mysql_query("SELECT proj_title, proj_decrip, proj_imagepath FROM project WHERE proj_section=imagery LIMIT 2");

well i would like it to come from one table as i have already created that table and it would save me some hassle.

wrathican · May 11, 2007

mpharo is right there is only one table, but thank you both for your help.

StormTheGates, im afriad i find your query very confusing and hard to follow. i find mpharo's a bit easier.

unfortuately i cannot test it right now as i do not have access to my projectbut i will post again to let you know how it goes.

thanks

Wrathican

wrathican · May 13, 2007

hmm i seem to get this when using it:

Warning: mysql_fetch_row(): supplied argument is not a valid MySQL result resource in /home/fhlinux178/t/test.ls12style.co.uk/user/htdocs/portfolio/pages/portfolio.php on line 56

any idea why?

mpharo · May 14, 2007

That error means that the variable that it is linked to is not a valid source...so either your query failed or did not return any results...or your have the wrong variable name in the function...

wrathican · May 15, 2007

hmm i am using the code you suggested modeed a little bit as a baseline see how it works but it doesnt. am i doing somethiing wrong?

[b]$query = "SELECT proj_title, proj_decrip, proj_imagepath FROM project GROUP BY proj_title ASC";

$result = mysql_query($query,$cxn);[/b]

echo "<table><tr><td>";

while($m = mysql_fetch_row($result)){

if ($title != $m[proj_title]){
          
echo "<table><tr><td colspan=\"3\">" . $m[proj_title] . "</td></tr><tr><td>" . $m[proj_decrip] . "</td><td>" . $m[proj_imagepath] . "</td></tr>";

}else{

echo "<tr><td>$m[proj_decrip]</td><td>" . $m[proj_imagepath] . "</td></tr>";

}

$title=$m[proj_title];

}

echo "</table></td></tr></table>";

the bold parts are the bits i modded. i still get the error

mpharo · May 16, 2007

mysql_fetch_row(); is used to return a single result, what you have is an array of results, so you need to use the mysql_fetch_array(); function...

wrathican · May 21, 2007

i have changed the array for mysql_fetch_array but still it fails.

i have enough data in my database for it to return at least one result.

ARGH! whats going on?

mpharo · May 21, 2007

fails with what error?

wrathican · May 21, 2007

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/fhlinux178/t/test.ls12style.co.uk/user/htdocs/portfolio/pages/portfolio.php on line 57

mpharo · May 22, 2007

try this...

$result = mysql_query($query) or die(mysql_error());

echo $result;

you should get an error from your mysql server giving a reason as to why your query isnt working.

Sign In

[SOLVED] Displaying multiple items from database

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information