Jump to content


Photo

Some help with sql_query


  • Please log in to reply
4 replies to this topic

#1 foreverhex

foreverhex
  • Members
  • PipPipPip
  • Advanced Member
  • 91 posts

Posted 30 June 2006 - 07:08 PM

Ive been using php for a while now, but I am still new at MySQL. I had a question about how to retrieve data from a sql table. i believe I use the sql_query to do it but Im not sure. Below is my example.

  • -ppl submit art and info with it
    -it stored to Mysql under the table "art" with a row called medium (photo, draw, cg)
    -there is a page that pulls up this art called "art.php"
    -if you goto the photography page (art.php?medium=photo) it shows only photos
    -if you go to drawing ect ect.
    -how do i make it grab only the art i request instead of the entire art table?
    -also how do I go about making a flexible html table to desplay this info?
    -is it hard to do pagination with all this?

Thanks for the help, someone always comes through and hits the nail on the head.

#2 gijew

gijew
  • Members
  • PipPipPip
  • Advanced Member
  • 240 posts
  • LocationCalifornia

Posted 30 June 2006 - 08:19 PM

I may be able to answer a few of those questions...

If each row has a unique number tied to it in MySQL called a primary key, you can use that to easily and safely display an individual row.

Example: <a href="art.php?photo=medium&photoID=1">Click for medium photo</a>
You can write a simple conditional or switch() to get it to display what you're looking for (I prefer switch).

This is just a simple guestimation of what to do.

<?PHP
switch ($_GET['Photo') {
  default:
    echo 'whatever it is you want to be displayed by default.';
  break;

  case 'medium';
    // If the user has opted to select an individual photo, store the WHERE clause in a variable so we don't have to double up on our work.
    if ($_GET['photoID']) {
      $PhotoConditional = 'WHERE photo_id = $_GET[photoID]';
    }
    $SQL_Photo = MySQL_Query("SELECT photo FROM art $PhotoConditional");
    while ($r = MySQL_Fetch_Array($SQL_Photo)) {
        echo $r['photo'].'<br />';
    }
  break;
)
?>

The above switch() should in theory display either all data or a single photo.  If you want all, just don't include the photoID in the URL.

Pagination is an entirely and painful subject.  I recommend going into the tutorials on this website and read the one there to get a better understanding of what to do.

I'm considered good looking in some countries

#3 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 01 July 2006 - 12:30 AM

art table
-----------
art_id (PK)
artist_id
image_path
medium (= 'photo', 'draw' etc)

If user selects photo page

SELECT * FROM art WHERE medium = 'photo'
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#4 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 July 2006 - 05:13 AM

The pagination is really the hardest part of all this... you have run some fancy SQL to get the entire result set count efficiently.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#5 foreverhex

foreverhex
  • Members
  • PipPipPip
  • Advanced Member
  • 91 posts

Posted 11 July 2006 - 01:37 PM

Thx Barand for helping me on my table layout and gijew for the sample code. I tried it our and its seems to be working pretty good. Ill do some altering and send a link so that you guys can check it out when its done. THX!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users