PHP/MySQL PLEASE HELP

[ansonb]

I am having issues in writing a program. The basic idea is a internet radio website. I have a page listing all shows on the station with a brief description. When you click on a specific show from that page, i need it to open a page showing more detail on just that show. The show list page is loading with no problem, but the show detail pages keep coming up with "Error in your sql syntax at line 1"

 

See what I have at http://www.eliteweboffers.com/lwrs/shows.php

 

Here is the code from config.php

 

<?php

mysql_connect($host,$user,$password);

mysql_select_db($db);

//Functions

function show_list()

{

$query = "SELECT * FROM shows"; 

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

while($row = mysql_fetch_array($result)){
echo " <table border='0'>
         <tr>
          <td rowspan='2'>
           <a href='". $site ."show_page.php?". $row['id'] ."' name='show_id' value='". $row['id'] ."'>
           <img src='". $site,$row['picture'] ."' height='180' width='180'></img></a>
  </td>
          <td> "
           . $row['show'] .
" </td>
         </tr>
 <tr>
          <td> "
           . $row['description-short'] .
        " </td>
 </tr>
        </table> ";}
}

function show_page()

{

$show_id=$_POST[show_id];

$query = "SELECT * FROM shows WHERE id = $show_id"; 

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

while($row = mysql_fetch_array($result)){
echo " <table border='0'>
         <tr>
          <td rowspan='2'>
           <img src='". $site,$row['picture'] ."' height='180' width='180'></img>
  </td>
          <td> "
           . $row['show'] .
" </td>
         </tr>
 <tr>
          <td> "
           . $row['description-short'] .
        " </td>
 </tr>
        </table> ";}

}

?>

 

If i change the line

$show_id=$_POST[show_id];

to

$show_id=1;

 

It loads the show detail page properly for the first show. I don't want to have to use "if" statements for every single show id.

 

What am I doing wrong ? Can anyone help ???

 

Here is the code from shows.php

 

<?php

include ('/home/ansonb/lwr_config.php');

show_list();


?>

 

Here is the code from show_page.php

 

<?php

include ('/home/ansonb/lwr_config.php');

show_page();


?>

 

 

Any help would be greatly appreciated.

 

Thank you !

[AyKay47]

1. you should be checking for $_POST['show_id']; being set with isset before you assign it to a variable, or do anything that involves using it.

 

2. you should be debugging your query by using mysql_error

 

3. update the relative line to:

 

$show_id=$_POST['show_id']; //add quotes around index

[DavidAM]

Unless I am reading the code wrong, you are not generating a valid HTML link in the show_list() function. In the code you have (I chopped part of it out):

 

// Just some values for the example
$site="http://www.mysite.com/"
$row['id'] = 4;
$row['picture'] = 'theshow.jpg';

// Your code 
echo " ... 
    <a href='". $site ."show_page.php?". $row['id'] ."' name='show_id' value='". $row['id'] ."'>
    <img src='". $site,$row['picture'] ."' height='180' width='180'></img></a>
    ... ";

 

I think you will get:

 

<a href='http://www.mysite.com/show_page.php?4' name='show_id' value='4'>
<img src='http://www.mysite.com/theshow.jpg' height='180' width='180'></img></a>

 

which is not generating a link that will help you at all. I think you need:

 

// Just some values for the example
$site="http://www.mysite.com/"
$row['id'] = 4;
$row['picture'] = 'theshow.jpg';

// Your code 
echo " ... 
    <a href='". $site ."show_page.php?show_id=". $row['id'] ."'>
    <img src='". $site . $row['picture'] ."' height='180' width='180'></img></a>
    ... ";

 

which should look like:

<a href='http://www.mysite.com/show_page.php?show_id=4'>
<img src='http://www.mysite.com/theshow.jpg' height='180' width='180'></img></a>

 

 

Also, when you are using a query string (the "?" on the link), you get the values from $_GET not from $_POST. So, your show_page() function should be using $_GET instead of $_POST.

 

Other things in that code you might want to think about:

1) For the most part, you are using concatenation (i.e. "A" . "B") , but in a couple of places you use the comma (like in the image source). The comma works fine in an echo statement, it just separates variables to be echoed. But anywhere else, you would want concatenation there as well. It confused me for a while (with all of the quotes and stuff). It is generally a good idea to pick one method or the other and be consistent.

 

2) You are only using a couple of fields from your select statement. For performance, it is best NOT to use "SELECT *" (unless you need every field in the table). You should list the fields you want in the select statement ("SELECT id, picture, show, `description-short` ...").

 

3) Using a dash in a column name is not a good idea in SQL. The server will think it is a subtraction statement unless you use the back-ticks (as I did in #2 above). Use an underscore ("description_short") or camel case ("descriptionShort").

 

4) In show_list(), you are generating an HTML table for each and every show. Unless there is a reason to have that many tables, I would start the table before the while-loop and close it after the while-loop, and then create a row ("<TR>...</TR>") for each record inside the while-loop.

 

5) I don't know why you have a while-loop in the show_page() function. I would think there would be only one row with the specified show_id. It doesn't hurt, much; but for a single-row result, it is completely unnecessary. If you leave it in, see #4 above.

 

6) AyKay47's responses are also valid (just substitute $_GET in place of $_POST).

[ansonb]

Thank You,

 

I will try this and see what happens.

[fenway]

And then confirm that there isn't a mysql issue so that I can move this thread to PHP help instead.

[ansonb]

Thank You DavidAM. It works. I am a little rusty, haven't done coding in a while.

 

I'm not quite sure what you meant about the commas in #1 of you reply. Can you show me a code example of what you mean? Anything to make it less confusing helps.

 

Also, in #5 you suggested not using a while loop since I only need one row. What should I use instead ? And can you please post code example of this ??

 

Thank You.

 

PS Sorry fenway. I posted to this topic by accident. You can move it.

 

 

[DavidAM]

I'm not quite sure what you meant about the commas in #1 of you reply. Can you show me a code example of what you mean? Anything to make it less confusing helps.

In your echo statement, where you are building the IMG tag, you used a comma between the $site and $row['picture'] variables. But you were using periods everywhere else. In echo, this works so:

$var1 = 'Hello, ';
$var2 = 'World';

// These two statements produce the same output
echo $var1, $var2;
echo $var1 . $var2;

// However, the comma will not work in other places
$statement = $var1, $var2; // NOT valid
$statement = $var1 . $var2; // Valid

print($var1, $var2); // NOT valid
print($var1 . $var2); // Valid

Mixing the two in that long statement, made it difficult to read (or maybe I'm just not used to seeing them). It is just usually better to be consistent.

 

Also, in #5 you suggested not using a while loop since I only need one row. What should I use instead ? And can you please post code example of this ??

 

If you only have one row, you can leave the while out and just retrieve the data.

$query = "SELECT * FROM shows"; 

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

$row = mysql_fetch_array($result);  // No WHILE loop here
echo " ...
           <a href='". $site ."show_page.php?". $row['id'] ."' name='show_id' value='". $row['id'] ."'>
           <img src='". $site,$row['picture'] ."' height='180' width='180'></img></a>
  ... "; // Took out the closing brace for the while we took out