[Resolved] [Resolved] connection to table for list in form not working

[Barb54]

Hi,
I'm trying to connect to 2 different tables for country and state in my form but they come up empty. I have checked spelling and it all looks right so here we go again with a possible query error, lol.

Here's what I have so far:

<?php
include("header.php");
/there is a check in the dbconnect file on the connection with the "die" code and failed to connect msg/
include("dbconnect.php");
?>
//first part of form left out to keep it short

<SELECT NAME="country">

<?php
$dblink=MYSQL_CONNECT($MySqlHostname, $MySqlUsername, 
$MySqlPassword) OR DIE("Unable to connect to database"); 
@mysql_select_db("$MySqlDatabase") or die( "Unable to select 
database"); 
?>
$sql="SELECT * FROM $countries ORDER BY country";
$result=mysql_query($sql,$dblink);
$num = mysql_num_rows($result);
$cur = 1;
echo "<ol>";
while ($num >= $cur) {
$row = mysql_fetch_array($result);
$country = $row["country"];
?>
<option><?php echo "$country" ?></option>
<?php
$cur++;
echo "</ol>";
?>
</SELECT>&nbsp;&nbsp;

<SELECT NAME="state">

<?php
$dblink=MYSQL_CONNECT($MySqlHostname, $MySqlUsername, 
$MySqlPassword) OR DIE("Unable to connect to database"); 
@mysql_select_db("$MySqlDatabase") or die( "Unable to select 
database");
?>

$sql="SELECT * FROM $states ORDER BY $state";
$result=mysql_query($sql,$dblink);
$num = mysql_num_rows($result);
$cur = 1;
echo "<ol>";
while ($num >= $cur) {
$row = mysql_fetch_array($result);
$state = $row["abbrev"];
?>
<option><?php echo "$state" ?></option>
<?php
$cur++;
echo "</ol>";
?> 
</SELECT></P>

Any ideas? Thanks in advance, Barb

PS if you respond please please please use the same $variables I have used here so I don't get confused lol. A new one will be easier to spot. tytyty lol

[AstroTeg]

You're queries have issues. Here's what you have:

$sql="SELECT * FROM $countries ORDER BY country"; 
// // //  and   // // //
$sql="SELECT * FROM $states ORDER BY $state"

Notice the double quotes and then this: "SELECT * FROM $countries ORDER BY country"

Because you used double quotes, PHP is going to swap out all variables it finds with a value. So when PHP gets done with this string, it probably looks like this:

"SELECT * FROM ORDER BY country"

I'm not sure what you're table name is. Is it just countries? If so, you'd want something like this instead:

"SELECT * FROM countries ORDER BY country"

Same goes for the query for states.

[bastien]

Have yo echoed out the sql to ensure that your $countries and $state are actually set

before you execute the query add the line:

echo $sql;

and see if the query is well formed. You can test this by copying the sql and pasting into phpmyadmin or something similar.

[Barb54]

Hi Astroteg,
You were absolutely correct on the $countries and $states in the SELECT statment. I corrected those but the selection list will still not be displayed.

I looked at the "view" source and some of the php code is visible. Does that mean my starting and ending php tags are out of balance or just not there in proper placement? I have tried a couple of different things but nothing has changed it yet, lol.

That is something I yet have to learn: When, why and where on the <?php and ?> tags. That is confusing to me right now as a newbie. Why would you need a new tag if you just connected to the db and are excecuting php code, not html?

Help on the tags in this code would be most appreciated.

Thanks,
Barb

[AstroTeg]

Actually, yes you have some extra ?>
tags in there - mainly right after you do all your database connecting, but before you define the sql statement. You should be able to remove the extra ones and be ok.

This is just coding practice, but I'll give you an example of what I usually do in this case:

<?
$dblink=MYSQL_CONNECT($MySqlHostname, $MySqlUsername, 
$MySqlPassword) OR DIE("Unable to connect to database"); 
@mysql_select_db("$MySqlDatabase") or die( "Unable to select
database"); 

$sql="SELECT * FROM $countries ORDER BY country";
$result=mysql_query($sql,$dblink);
echo "<ol>";
while($row = mysql_fetch_array($result)) {
  echo '<option>' . $row['country'] . '</option>';
}  // end while($row = mysql_fetch_array($result))
echo "</ol>";
?>
</SELECT>&nbsp;&nbsp;

Things to look at are is the where loop. As you get comfy with PHP, this will make more sense. I've combined the row fetch operation along with the while loop. When the fetch runs out of records, it will bounce out of the while loop. This means you don't have to keep a counter or know the number of rows you have to loop. I've also combined the code in the while loop down to a single line. What you had will work just fine. I figure I take a moment and show some tricks...

If you want to get fancy, you can build an all purpose list box PHP function that allows you to pass a query to it and it puts together the HTML for you. I have one if you're interested...

[Barb54]

Thanks sooooo much for your help! I have really struggled with writing my own code instead of copying snippets that I find. I found an excellent tutorial yesterday and this script is a result of that and I did it ALL myself and am very proud of that, lol.

It's nice to hear that it would have worked except for the tags. That means I learned something FINALLY, lol.

Thank you for the help on the mod to make it cleaner. I really appreciate that!

YES, I have been wanting to learn more about functions but haven't found any real help in that area yet. I understand you can create your own if you run pieces of code over and over. I just don't know how to put a function together or how to call one from another page. I want to use Smarty Template Engine and I see they rely heavily on functions. Some understanding of how they are put together would be very nice. Do you know of a tutorial or any explanations on the subject? I would like help in that area very much.

Thanks again and I will mark this post as resolved if I can figure out how, lol. You can PM me on the function info if you like, please.

Barb

[bastien]

Hi Barb,

Function are incredibly useful and very simple to use

either in one page or across several...just include the file containing the functions (I generally call mine common.php)

<?php
//common.php
function hello()
{
  echo "Hello there!";
}
?>

<?php
//some other page.php
//include the common.php file
include("common.php");

for ($i=0;$i<10;$i++){

   //call the function
   hello();  //you can pass data to the fucntion by including it in the brackets

}//end for loop

?>

The above is pretty simplistic but gives you an idea of exactly how functions can interact between pages. I tend to use this a lot for forms. I create the form in a function and set the VALUE attribute for the form element. When the form is called the first time, there is no data in the form and the VALUE attribute is empty so nothing shows in the text box. If there is error checking and the user inputs something wrong I can call the form again and have all the previously input data set for the user to correct.

hth