OMG, variables help

Probobly very simple.....

[bobnew32]

Ok what I want to do is have my shows.php act as shows.php?show_num=(number). Now I know how to connect to the server and connect to the right database and got as far as typing:

if ( isset($_GET['show_num']) and ctype_digit($_GET['show_num']) )
$show_num = $_GET['show_num'];
else
$show_num = -1;

$sql = 'SELECT * FROM `Show_Data` WHERE 1 AND `show_num` = $show_num LIMIT 0, 30';

**(obviously the database is Show_Data and show_num is the number to identify each show).**

So now when you type in show.php?show_num=1 it creates a variable $show_num and the page "gets it" and selects the row that has $show_num of (some number).

What I want to happen next is to be able to use all the other data the page selects from the database; it selected everything and I want to be able to display it in different parts of the page. I have show_bio, show_name, and show_station in that row, but I have not idea how to display that information for that row! Please help me.

[daisho]

$result=mysql_query($sql);

if( $row=mysql_fetch_array($result,MYSQL_ASSOC) ) {
print "I can now display information from the database. Like the show name: ${row['show_name']} or any other field in the row...";
} else {
print "Show not found...";
}

[bonanza]

First you have to connect to the database:

$db = mysql_connect("localhost", "username", "password");
mysql_select_db("database",$db);

Then execute the sql statement:

$result = mysql_query($sql);

This gets the data and puts it in an array called myrow:

$myrow = mysql_fetch_array($result);

Now you can reference your data with the following variables:

$myrow["show_bio"]
$myrow["show_name"]
$myrow["show_station"]

The above assumes you want and expect only a single row. You can keep calling

$myrow = mysql_fetch_array($result);

to get the other rows, best done in a while loop.

Check out the MySQL Functions [php.net] doc as well where you'll see all your options and methods for error checking.

Once you get this working, be sure to check out the extract function [php.net] which stores values from a query into easier to use variable names.

[bobnew32]

Bonanza, when I do this:

<?php
include 'Includes/dbConnect.php';
if ( isset($_GET['show_num']) and ctype_digit($_GET['show_num']) )
$show_num = $_GET['show_num'];
else
$show_num = -1;
$sql = 'SELECT * FROM `Show_Data` WHERE 1 AND `show_num` = $show_num LIMIT 0, 30';
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);?>

And the dbConnet.php code is

<?php
$conn = mysql_connect("localhost", "username", "password");
mysql_select_db ("newbobbo_Sitemain");
?>

I get this error:
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/newbobbo/public_html/Showstemplatephp.php on line 10

Line 10 code is $myrow = mysql_fetch_array($result);?>

[bonanza]

If you had used Daisho's example, it would have handled your case more gracefully. That error usually means that the query returned no data.

print out your $sql variable to make sure your SQL statement's good:

echo "<br>$sql<br>";

If it's not immediately apparent, execute that query in PHPmyAdmin or command-line mysql to see what happens with it.

[bobnew32]

It just echos back

SELECT * FROM `Show_Data` WHERE 1 AND `show_num` = $show_num LIMIT 0, 30

Is that a bad thing, I put the code you gave me into shows.php and viewed it with shows.php?show_num=1

[bobnew32]

Ok I changed some code around and it returned:
Resource id #4, so it went through

I changed my select statement to $result = mysql_query("SELECT * FROM `Show_Data` WHERE 1 AND `show_num` = $show_num LIMIT 0, 30");

and all my code so far is

<?php
include 'Includes/dbConnect.php';
if ( isset($_GET['show_num']) and ctype_digit($_GET['show_num']) )
$show_num = $_GET['show_num'];
else
$show_num = -1;
$result = mysql_query("SELECT * FROM `Show_Data` WHERE 1 AND `show_num` = $show_num LIMIT 0, 30");
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);?>

but I now have two $result variables, is that a problem, how should I deal with it?

[willybfriendly]

"WHERE 1 AND `show_num` = $show_num"

Why the WHERE 1?

It almost looks like you are trying to test the validity of $show_num, but I can't really tell from this code snippet. If that is the case why not:

else
exit;

instead of:

else
$show_num = -1;

Alternatively you could set $show_num to false and test this way

if ( isset($_GET['show_num']) and ctype_digit($_GET['show_num']) )
$show_num = $_GET['show_num'];
else
$show_num = false;

if($show_num)
{
$sql = 'SELECT * FROM `Show_Data` WHERE `show_num` = $show_num LIMIT 0, 30';
$result = mysql_query($sql);
$myrow = mysql_fetch_array($result);
}
else
echo "That is not a valid show...";
?>

echo your $result and $myrow. I'm guessing that you are passing mysql_fetch_array an empty variable.

WBF

[bonanza]

It just echos back
SELECT * FROM `Show_Data` WHERE 1 AND `show_num` = $show_num LIMIT 0, 30

That's because you were using single quotes around the string defining the sql. Use double quotes so your variable gets replaced properly. Single quotes are literal.

You solved that by putting the sql in double quotes right in the mysql_query statement.

but I now have two $result variables, is that a problem, how should I deal with it?

All you really need is this:

$sql = "SELECT * FROM Show_Data WHERE show_num = $show_num"; // note the double quotes
$result = mysql_query($sql); 

and get rid of the other $result=... line.

I also got rid of the backquotes, the '1 and', and the LIMIT statement at the end. Looks like you're getting this out of PHPmyAdmin. You don't need those.

[edited by: bonanza at 12:10 am (utc) on May 4, 2003]

[daisho]

Use double quotes for the SQL ie:

$sql = "SELECT * FROM `Show_Data` WHERE `show_num` = $show_num LIMIT 0, 30";

Also why do you have backtics (`) around "Show_Data" and "show_num"? These are definatly not required and could inface cause you some problems.

daisho.

[bobnew32]

Ok guys I think I coded all your comments and ended up with this code:

<html>
<?php



$conn = mysql_connect("localhost", "username", "pw");

$db= mysql_select_db("newbobbo_Sitemain");

$show_num = $_GET['show_num'];
$sql = "SELECT * FROM Show_Data WHERE show_num = $show_num";
$result = mysql_query($sql);
$row = mysql_fetch_array($result);?>

And yet I ended up with this error
Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/newbobbo/public_html/shows1.php on line 14

BTW, I thank all of you for helping me out, this is kind of difficult for me so I thank you.

[bonanza]

Print out $sql to make sure it's ok.

Is show_num a numeric field? If not, you need to surround $show_num with single quotes in the sql statement.

[bobnew32]

Ok I fixed it (must have had an extra ' or somthing in there :), but thanks guys it works and now I can add the 23 other fields (already did 5 just make sure it was ok and it was) and I wanted to say thx for your help, it has been very confusing and thx for your time, you guys just helped create the main form for my website!