PHP Mysql question - pulling single entry - hopefully easy answer??

[ofmyst]

I have a page that shows a series of paintings.  The painting info is pulled from mysql database.  A viewer can click to enlarge the picture and a new page will open up with a larger version of the picture as well as detailed info.  I need help pulling just a single entry from the database.    I have a field called ID that has numbers, 01, 02, etc.  That field is indexed (if I have done that correctly).

 

Here is what I have: 

 

<table width=100%>

<tr>

 

<?php

$con = mysql_connect("");

if (!$con)

  {

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("paintings", $con);

 

$result = mysql_query("SELECT * FROM tinkers");

 

 

 

while($row = mysql_fetch_array($result))

{

    echo '<td align="center"  style="color:black; font-family:Garamond; font-size:20px" ><br>'.

      $row['Image'] . "<br><br>\n".

        $row['Link'] . "<br>\n".

        $row['Title'] . "<br>\n".

        $row['Medium'] . "<br>\n".

        $row['Size'] . "<br>\n ";

 

 

mysql_close($con);

?>

 

<font size=1>© S.D. ROSS </font><br><BR>

<font size=3><B>All Rights Reserved</b><BR><BR>

</td>

</tr>

</table>

 

I am not sure where to tell it to pull the particular painting (eg., ID 01)

 

Thank you for any help.  I am cross eyed from searching and hope it is an easy solution.

[lynxus]

$result = mysql_query("SELECT * FROM tinkers");

 

to

 

$result = mysql_query("SELECT * FROM tinkers WHERE id = x LIMIT 1");

 

Is this what you mean ?

 

I suppose you will want to do summat like.

 

myfile.php?id=1

 

then do summat like

 

$whatid = $_REQUEST["id"];

 

$result = mysql_query("SELECT * FROM tinkers WHERE id = $whatid LIMIT 1");

 

On second thoughts u will also what to do some checking on the request data. I suppose the above is very open to sql injection etc.

 

might want to use the removeslashes or whatever it is and do some other checks.

[ofmyst]

I am sorry but  I am truly a novice and I wasn't able to follow what you were saying. 

[dannyb785]

First off, if you're indexing your paintings by 01, 02, etc you're making it more complicated than necessary. Just set the painting id to primary and auto_increment and make it a type INT. That's one thing..

 

secondly, if you're grabbing just one painting and you know the id, you'd just do something like:

 

$result = mysql_query("SELECT * FROM painting WHERE id='$id' ");
if(mysql_num_rows($result) == 0) echo "no painting found";
else
{
  extract($row);
  // all row variables are now ready to be processed
}

 

I almost always do the extract() function, but you don't have to. If you didn't know, extracting makes it so that the column name in the rows become variables. So if you had a column 'price' then once you do extract(), there will be a variable $price with the value in it. If you don't want to extract, just do $row['price'] for the same thing.

[ofmyst]

I think I followed your directions - I now have a field called id,set to int, auto-increment, primary.  It automatically numbers 1, 2, etc..  paintings is the database, tinkers is the table.  I made the following changes:

 

 

<table width=100%>

<tr>

 

<?php

$con = mysql_connect("");

if (!$con)

  {

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("paintings", $con);

 

$result = mysql_query("SELECT * FROM tinkers WHERE id='$1' ");

if(mysql_num_rows($result) == 0) echo "no painting found";

else

 

{

    echo '<td align="center"  style="color:black; font-family:Garamond; font-size:20px" ><br>'.

      $row['Image'] . "<br><br>\n".

        $row['Link'] . "<br>\n".

        $row['Title'] . "<br>\n".

        $row['Medium'] . "<br>\n".

        $row['Size'] . "<br>\n ";

 

 

mysql_close($con);

?>

 

<font size=1>© S.D. ROSS </font><br><BR>

<font size=3><B>All Rights Reserved</b><BR><BR>

</td>

</tr>

</table>

 

I end up with a blank page.  I think I have misunderstood what do do in the WHERE part but I am not sure what?

[jandrews3]

Try

$link = mysql_connect("") or die("Could not connect: ".mysql_error());
$query = "SELECT * FROM tinkers WHERE id = '$id'";
$result = mysql_query($query) or die("Could not perform query: ".mysql_error());
while ($row = mysql_fetch_array($result));

 

• you will need to pass the painting's id which you wish to see to this page

• this code will connect to the database,

• pick out the row which has the id value as the variable $id,

• any variable information will be accessed by using $row['id'], $row['name'], $row['caption'], $row['painter'], etc...

[dannyb785]

Ok, well remember, when you're displaying info that can change as the user sees fit, you will be using $_GET variables which are found in the address bar after the file name. for example: if you have index.php?id=1, within index.php, you will have one variable in the $_GET array which is $_GET['id'].

 

So for you, to view a specific painting, you will have a separate page that displays the painting info(or the same page, doesnt matter) and attached to the viewpainting.php or whatever, you'll have &id=NUMBER where NUMBER is the painting's id.

 

So what you need to do is get the specified variable with something like $id = addslashes($_GET['id']); Then you'd do the query and have WHERE id='$id' And lastly, you have to run the $row = mysql_fetch_assoc($result) after doing the query. This converts the raw query data into an array named $row. THEN your code should work. Test it out by doing your viewpainting.php?id=1 but if you dont have a painting with an id=1 then it wont find any rows. So you're going to need further checks to make sure it's a valid painting(worry about that later). Give it a try and make sure to post your code if you have trouble.

[ofmyst]

I am making a mud pie of this (and I thought this would be the easy part ::: smile :

 

What I think I am trying to say is - look for the id2 only and echo the info in id2's fields.    (I will make duplicate .php pages for id3, id4 etc.). 

 

Here is the code I have now.  I know you have answered this already, and I appreciate your patience, but apparently I am incapable of understanding the answer!

 

I pared it down to make it easier - I hope.

--------------------------

 

<?php

$con = mysql_connect("");

 

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

 

mysql_select_db("paintings", $con);

 

$result = mysql_query("SELECT * FROM tinkers WHERE $id = '$2' ");

if(mysql_num_rows($result) == 0) echo "no painting found";

 

else

{

  extract($row);

  // all row variables are now ready to be processed

}

 

mysql_close($con);

?>

 

------------------------------

 

When I run it like this I get no painting found, but I do have an id2 entry in the mysql.

 

 

[dannyb785]

no no no. Are you using a database? If so, it's counter-productive to make a different page for each painting. Database uses like this are there so that you only need one generic page that displays any painting. Why even do a query on the page if the page id1.php will only ever get painting 1's info? You get to supply the painting id in the address(like I said before) and then just grab the respective id from the database and the info will be ready for you to display.

[ofmyst]

Well, even better.  Now I know why I could not make heads or tails of things - I was missing the concept.  Let me re-read all of the notes and see if I can't figure it out.  I imagine I will be back.  Thanks again. 

[ofmyst]

I'm so embarassed but I have read through the notes many times and played around, but cannot quite get it.  My paintings page, (if it helps you can find it at:  http://www.sharondross.com/images/tinker/tinker.php).  When the painting is clicked on I want it to click to a page that autofills.  An example of a non-autofill but the structure I would like to have is:  http://www.sharondross.com/images/tinker/207.html.  I am trying to set up the PHP.  The PHP I have for the main page - tinker.php - is: 

 

"

<table width=100%>

<tr>

 

<?php

$con = mysql_connect("");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("paintings", $con);

 

$result = mysql_query("SELECT * FROM tinkers");

 

// initiate counter

 

$i = 2;

 

 

while($row = mysql_fetch_array($result))

{

echo '<td align="center"  style="color:white; font-family:Garamond; font-size:20px" ><br> <a href='.

$row['Link'] . "><img src=\n".

$row['Image TN'] . " border=0  class=borderimage onMouseover=borderit(this,'black') onMouseout=borderit(this,'white')></a> <br><br><a href=\n".

      $row['Link'] .">\n".

$row['Title'] .  "</a><br>\n".

      $row['Medium'] . "<br>\n".

      $row['Size'] . "<br><br><br><br></td>\n";

 

// if $i is equal to , echo a new table row

if($i == 2 )

  {

    echo "</tr><tr> ";

    }

 

// if $i is equal less than 3, echo a new table row then end 2 ptg table

    elseif($i == 3 )

    {

    echo '</tr> </table> </td> </tr> <tr> <td colspan=4>';

  echo ' <table width=100%><tr><td>   </td> </tr> <tr>';

    }

 

    // if $i is dividable by 3, echo a new table row

    elseif($i%3 == 0)

    {

    echo '</tr>';

  echo '<tr><td colspan=3><img src="/images/misc/blankline.gif" width=925></td></tr>';

  echo  '<tr>';

    }

 

// increment counter

  $i++;

}

 

echo '</tr>

</table>';

 

mysql_close($con);

?>

</td></tr></table>

 

"

 

I don't exactly get where the GET and the WHERE go. 

 

I am sorry to even ask again, I know it is annoying, but to hand fill in each of these pages it such a pain, and I would love to have it pull the information.  Thank you .

[DarkWater]

<table width=100%>
<tr>

<?php
$con = mysql_connect("");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("paintings", $con);
$id = (int) $_GET['id'];
$result = mysql_query("SELECT * FROM tinkers WHERE id=$id");

// initiate counter

$i = 2;


while($row = mysql_fetch_array($result))
{
echo '<td align="center"  style="color:white; font-family:Garamond; font-size:20px" >
<a href='.
   $row['Link'] . "><img src=\n".
   $row['Image TN'] . " border=0  class=borderimage onMouseover=borderit(this,'black') onMouseout=borderit(this,'white')>[/url]

<a href=\n".
          $row['Link'] .">\n".
   $row['Title'] .  "[/url]
\n".
          $row['Medium'] . "
\n".
          $row['Size'] . "



</td>\n";
                        
// if $i is equal to , echo a new table row
if($i == 2 )
  {
    echo "</tr><tr> ";
    }

// if $i is equal less than 3, echo a new table row then end 2 ptg table
    elseif($i == 3 )
    {
    echo '</tr> </table> </td> </tr> <tr> <td colspan=4>';
  echo ' <table width=100%><tr><td>   </td> </tr> <tr>';
    }

    // if $i is dividable by 3, echo a new table row
    elseif($i%3 == 0)
    {
    echo '</tr>';
   echo '<tr><td colspan=3><img src="/images/misc/blankline.gif" width=925></td></tr>';
   echo  '<tr>';
    }

// increment counter
  $i++;
}

echo '</tr>
</table>';

mysql_close($con);
?>
</td></tr></table>

 

Try that.

[ofmyst]

I tried that code and lost everything in the echos.  Also am I using [/url] or </url> and if I use that do I replace <a href=" with <url="  ?

 

Thank you again.

[DarkWater]

Change this line:

$result = mysql_query("SELECT * FROM tinkers WHERE id=$id");

 

To:

$result = mysql_query("SELECT * FROM tinkers WHERE id=$id") or die(mysql_error());

 

And post any errors that pop up.

[ofmyst]

No errors, but still no paintings.  See working page:  http://www.sharondross.com/images/tinker/tinker2.php .

[ofmyst]

I'm sorry I didn't get back last night with more specific information.  I was too sleepy!  I played around and it seems  what makes the paintngs disappear is:  WHERE id=$id  - from the line -  $result = mysql_query("SELECT * FROM tinkers WHERE id=$id"); line.  When it is just  $result = mysql_query("SELECT * FROM tinkers");  the paintings show up.

 

 

Also [/url] does not work but </url> does.  However, I do not know if that is an appropriate exchange.  Is it okay to close <a href=" "> with </url> rather than </a> or does the opening have to be something else?

 

I really do appreciate all of the help.