Autofill fields in a form

[Modern Merlin]

Ok please forgive me, as I am really new to the whole PHP programming...

What I am attempting to do is on a form page create a drop down list that is populated from information in a database. For example a list of company names, then with this that would populate one more drop down list with the department they belong to if any.

Once both of those are chosen and the user hits submit it would populate text fields below. For example Name, company, phone number, email so on and so forth.

At this point the first drop down list box shows up but is empty. Here is the code I have for that:

<select name="comapny_list" size="1" id="company_list"> 
<?php 
include "config.php";
 
//connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass) or die("Could not connect to MySQL");
 
//select the database
mysql_select_db($database) or die ("Could not select database");
 
// Filling up the selection box
 
$query = mysql_query ("SELECT * FROM " . $table . " WHERE 'Active' = '1'", $link) or die("<center>There was an error and we could load the Client list</center>"); 
$result = mysql_result($query); 
while ($row = mysql_fetch_array($result)){ 
$comp_name = $row2['company'];  
 
echo "<option value='$comp_name'>{$comp_name}</option>"; 
} 
?> 
<option value=""></option> 
</select> 

Any suggestions would be much appreciated! Thanks!

*Edit here is the error form the error log sorry about that...
Wrong parameter count for mysql_result() in /htdocs/forms/orderform2.php on line 29

PHP Warning:  mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /htdocs/forms/orderform2.php
 on line 30
 

[Atli]

Hi Merlin. Welcome to TSDN!

Try printing the query before you execute it, just to see that it looks as you expect it to. It's so easy to make a typo in there somewhere and it helps to see it all combined.

It is always a good idea to check whether your mysql result is valid to avoid the kind of warning you posted above. This can be done like so:

$result = mysql_query(...);
if($result) {
  # Rest of the code.
}
else {
  # Print error.
}
 

Note that your use of the mysql_result() function is not correct in your code. I would drop that and use the original result from the mysql_query() function.

I noticed that in your query you match a column, 'Active', against a number, '1'.
The field name itself should not be quoted, not unless you use back-ticks.

If the number is in fact the number 1, rather than the character 1, it should not be quoted either. I do suspect, however, that the 'Active' field is a CHAR(1) field, in which case your approach is the right one.

I'd recommend changing "SELECT * FROM ..." into "SELECT company FROM ..." because you are only using that one column. No need to fetch a bunch of columns you will never use. That only serves to slow down your server.

I'd also recommend changing from the mysql_fetch_array() function to the mysql_fetch_assoc() function to increase efficiency. The latter only creates an associative array, where the former creates a combination of both associative and indexed arrays.

[Modern Merlin]

I tried some of the things you suggested by starting with the printing the query to the screen. I also changed my query to company as per your suggestion.

What it returns is Resource id #4. This isnt anywhere in my database.

So currently all I am trying to do is select the company field and put it in a drop down list to select from, but I cant even get that to print to the screen.

[Atli]

I tried some of the things you suggested by starting with the printing the query to the screen. I also changed my query to company as per your suggestion.

What it returns is Resource id #4. This isnt anywhere in my database.

So currently all I am trying to do is select the company field and put it in a drop down list to select from, but I cant even get that to print to the screen.

What you are printing is the result ID you get from your MySQL server. What I meant is, print the actual query and the error. Something like:

# Create and execute query
$sql = "SELECT col from tbl"
$result = mysql_query($sql);
 
# Print query for debugging purposes
echo "<pre>$sql</pre>";
 
# Check result
if($result) {
  # Print all rows
  $rowno = 0;
  while($row = mysql_fetch_assoc($result) {
    $rowno++;
    echo "Row $rowno:<br />"
    foreach($row as $_k => $_v) {
      echo " - $_k = $_v<br />";
    }
  }
} 
else {
  # Print error
  echo "<b>Query failed:</b> ". mysql_error();
}
echo "</pre>";
 

[Modern Merlin]

I feel like an idiot LOL

I tried what you have there and I get a parse error....

Here is the code:

<?php 
include "config.php";
 
//connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass) or die("Could not connect to MySQL");
 
//select the database
mysql_select_db($database) or die ("Could not select database");
 
// Selecting form the database
 
$query = mysql_query ("SELECT Company FROM Order_Form" ) or die("<center>There was an error and we could not load the Client list</center>"); 
 
//echo $query;
$result = mysql_query ($query, $link);
 
echo "<pre>$query</pre>";
 
# Check result
if($result) {
  # Print all rows
  $rowno = 0;
  while($row = mysql_fetch_assoc($result)  {
    $rowno++;
    echo "Row $rowno:<br />"
    foreach($row as $_k => $_v) {
      echo " - $_k = $_v<br />";
    }
  }
} 
else {
  # Print error
  echo "<b>Query failed:</b> ". mysql_error();
}
echo "</pre>";

PHP Parse error: parse error, unexpected '{' in /htdocs/forms/orderform2.php on line 23

I changed the the line number to the correct one it is given me an error on. I am going over some of the lessons I took to see if I can figure it out, but any help would be much appreciated...

Modern Merlin

[post]

[php]
<?php

include "config.php";

//connect to the mysql server
$link = mysql_connect($server, $db_user, $db_pass) or die("Could not connect to MySQL");

//select the database
mysql_select_db($database) or die ("Could not select database");

// Selecting form the database
$query = mysql_query ("SELECT Company FROM Order_Form" ) or die("<center>There was an error and we could not load the Client list</center>");
// Put results in array
$result = mysql_fetch_array($query);

if ($result) {
$rowno = 0;

echo("<select name=\"comapny_list\" size=\"1\" id=\"company_list\">");

while ($row = mysql_fetch_assoc($query)) {

$rowno++;

foreach($row as $_k=>$_v) {

echo("<option value=\"" .$_v. "\">" .$_v. "");

}

}
echo("</select>");
}
else {
# Print error
echo "<b>Query failed:</b> ". mysql_error();
}
?>
[/php]

You forgot to add a ) at line 27.

But I fixed your code to do what I think you were asking take a look at it.

[Modern Merlin]

WOW that worked perfectly! THANK YOU!!!!

Now my question is, is there a way to select company as a field in one table if a field in that same table is active or in active? I know in MySQL I can select * from the table that are active by doing the statement: SELECT * FROM `Order_Form` WHERE `Active` = '1' This will pull up all active records.

What I am ultimately trying to do is to fill one drop down box with all the companies that are active and within that if the same company but different department places an order I need to pull up their record information. So I need a second drop down box to change the the dept if there is one. Then the text fields below will be filled in as per both of those selections. Then the rest of the form is filled up and written back to the database (which that part I have qworking fine.

Im thinking at this time I may need to create more than one table.. :(

Still working on this myself to see if I can figure it out but any nudge in the right direction would be awesome!

Modern Merlin

[pbmods]

Heya, Merlin.

So what you're saying is that you have a table of companies and a table of departments, and you want the values in the second drop down to be all departments for whatever company is selected in the first drop down. Is that right?

[Modern Merlin]

No I have one table.. With fields..

Name
Company
Dept
Phone
Then some choices made by checkboxes

Which the customer will fill out. Then the sales associate will pull up another page that will have a drop down box for companies (which now populates) and some companies will have different departments that order from them. So I will also need a dept drop down the populates as per the company chosen. Once that is done the text boxes below with name company dept, phone plus the checkboxes already marked by the customer will populate as per the company and dept fields above and then the sales associate will be able to choose more check boxes and text fields and that will write back to the database (which I need to over-write what is already in there so that it doesnt create another row in the table but replaces whats already in there).

I already have the part for writing back to the datbase figured out for the customer portion so the table will populate. I can now pull the company names into the drop down box and if I want to list ALL the departments I can list those too. Whichever way I do it, the text fields and and the check boxes have to populate by the company name and dept chosen. If there isnt a dept then the dept field is listed as none, which Im not so sure is a good idea.

Should I have a seperate table to house the departments? I have no problems starting this over because as I explained in the beginning I am new to this and havent really touched any programming in almost 4+ years LOL. Believe me I have no problem admitting when I need help :)

[pbmods]

Heya, Merlin.

How does your script know that there are multiple departments for a given company? Does it pull this info from a separate table, or does it search for all departments in the one table that match the selected company name?

[Modern Merlin]

well currently it pulls all the companies and then all the dept and then the user selects the correct one as per the client. Then it searches through the table for all companies that match each one. So there wont be more than one.

[Modern Merlin]

So I solved my own issue going by a much more simple method. I rebuilt the db to house clients and orders and just used the one constant between the two which is the order_id. Thank you all fo rhelping me!

Modern Merlin
~been up for 40 hours now... time for some shut eye...

[pbmods]

Heya, Merlin.

Glad to hear you got it working! Good luck with your project, and if you ever need anything, post back anytime :)