Forum Moderators: coopster
I am pretty new to PHP and MySQL.. I have taken on a project at work which is proving quite challenging (and time consuming!) This project is huge, so I am tackling only little bits at a time, so as to ensure that I understand what I am doing. And as I learn each new aspect, I add to it to fully enmesh with the overall project.
I have a page on which I enter information about a service order which upon sublittam will save information into a master table in my MySQL database. The page contains multiple drop-down menus which are linked to other tables. An example: the salesperson table, which contains entries for name, phone, cell, and email address. The drop-down menus update once information is entered into the specific table and the new entry will then be available to choose in the drop down menu.
My issue, which I haven't been able to figure out, is this. How do I query multiple tables and display the information on an "order review" page? I would like to be able to choose a salesperson from the drop down menu, and then have all the entries which are associated with the salesperson (phone, cell, email)display on the review page.
Perhaps in my noobishness I am not explaining myself correctly. If there is further information needed, I would be happy to provide it! Please keep in mind when viewing my tables below, that I am only adding one module in at a time, until I know that part is working, and then I move on to the next. By the time my project is complete, I will have a total of 11 dropdown menus which will need to be queried the same as the salesperson one which I am beginning with ;)
My tables:
Field Type Null Key Default Extra
id int(11) PRI auto_increment
diwtitle varchar(40) YES
dropdownacctmgr varchar(30) YES
acctmgr(where the salesperson info is kept:
Field Type Null Key Default Extra
id int(11) PRI auto_increment
acctmgr_name varchar(30) YES
acctmgr_phone varchar(11) YES
acctmgr_cellphone varchar(11) YES
acctmgr_email varchar(20) YES
Many thanks!
BadGoat the PHP n00nlet
I would recommend the purple spined PHP and MySQL Web Development book (Luke Welling, Laura Thomson). I think I was able to find one used online for a bit cheaper than in the store.
Excellent introduction to both and good USEFULL examples with full code included on CD.
It will really speed up your development. The online resources are great...but I still find myself going back to the book for a better example.
You're post really didn't make it clear to me what the problem exactly was...if you are querying multiple tables it would look something like:
$query="select * from Salesmen, Workorders where Salesmen.ID=Workorders.SalesmanID and Salesmen.ID=$id"
(Table.columnname)
troubleshooting:
if you get an error and no results put the following line in your code after setting the $query:
echo $query;
(try to see what doesn't match the proper syntax)
if you don't get the results you are expecting, put the following after the $mystuff=mysql_fetch_assoc($result) statement:
print_r($mystuff);
(you'll see an entire row that results from your query, then tweek the query to get what you are really after. Step the rows into an array and print_r($myarray) for even more help)
You do know there is a MySQL website...right?
I'll try to explain my issue better. I have one table called acctmgr which keeps info on the salespeople. I have columns in this table for name, phone, cell, and email.
I have an order entry page on which I have a dropdown menu with the salesperson name in it. I want to be able to select a name from this dropdown menu, and then when I hit submit, the next page displays not only the salesperson name, but also the phone, cell, and email address, which was pulled from the acctmgr table.
BadGoat, it seems you have a simple query really. I believe you are thinking you need some form of join here, but you don't (unless I am missing something). But it looks as if you have everything you need in the acctmgr table. So, when the user selects an option from the list, you take the value of that option and use it to get your information. A sample of how it might look on the second page follows:
$person = (isset($_POST['person'])) ? $_POST['person'] : '';
if ($person) {
$sql = "SELECT
acctmgr_name,
acctmgr_phone,
acctmgr_cellphone,
acctmgr_email
FROM
acctmgr
WHERE
acctmgr_name = '$person'
";
// execute your query,
// process the result set
}
If your value was based on the id, then you may need some form of JOIN. For example:
SELECT
acctmgr_name,
acctmgr_phone,
acctmgr_cellphone,
acctmgr_email
FROM
acctmgr
INNER JOIN othertablename
ON (acctmgr.acctmgr_name = othertablename.dropdownacctmgr)
WHERE
othertablename.id = 'somevalue'
;
Thank you for the reply. I too believe the resolution is pretty simple.. And I think your first posted potential fix is what I am seeking. To be sure, I want to copy here snippets of my code, and hopefully you can tell by looking at them whether it is the correct avenue for me.
Also, I have a follow-up question.. Once I select the right variables from the acctmgr table, can I then echo them just as I echo variables from the primary table? Or is there special syntax for displaying the data from the acctmgr table?
Thanks very much! If you need more information, I would be glad to post whatever you need so you can take a look at it!
Here is the snippet of code where the salesperson name is added to a drop down menu:
<?
//--- CREATE Account Manager SELECT ---
$sql = "SELECT DISTINCT acctmgr_name FROM acctmgr ORDER BY acctmgr_name";
$acctmgr_name = mysql_query($sql) or die($sql . '<br />' . mysql_error());
while ($row = mysql_fetch_array($acctmgr_name)) {
echo '<option value="' . $row['acctmgr_name'] . '">' . $row['acctmgr_name'] . '</option>';
}
?>
I believe that's what it is anyways, and I have made 2 pages to test adding just this portion to a database, and I cannot echo the variables which I have saved in the acctmgr table. Can you tell what I am doing wrong?
<select name="dropdownacctmgr" tabindex="4"><option value="notset">
<?php
$connect= mysql_connect("localhost","root","cpyter")
or die("Could not connect to database in localhost!");
$result=mysql_select_db("test2diw")
or die("Could not select that database!");
$joined = date("Y-m-d h:i:s");
$sqlquery = "INSERT INTO testdiw VALUES('','". $diwtitle ."','". $dropdownacctmgr ."','". $joined ."')";
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query!");
$sqlquery = "SELECT * from diw";
$dropdownacctmgr = (isset($_POST['dropdownacctmgr']))? $_POST['dropdownacctmgr'] : ''; if ($dropdownacctmgr) { $sql = "SELECT acctmgr_name, acctmgr_phone, acctmgr_cellphone, acctmgr_email FROM acctmgr WHERE acctmgr_name = '$dropdownacctmgr' "; // execute your query, // process the result set
}
[edited by: coopster at 7:18 pm (utc) on Mar. 16, 2005]
[edit reason] removed irrelevant code [/edit]
This is the error I am getting:
Notice: Undefined variable: acctmgr_phone in c:\program files\easyphp1-7\www\diw2.php on line 37
So in this case it would be dropdownacctmgr?<select name="dropdownacctmgr" tabindex="4">
Yes, you have that correct. Next, in your second page, you have to get the variable out of the posted form before you can use it in a query or anywhere else in your script for that matter ... see your problem ...?
$sqlquery = "INSERT INTO testdiw VALUES('','". $diwtitle ."','". $dropdownacctmgr ."','". $joined ."')";$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query!");
$sqlquery = "SELECT * from diw";$dropdownacctmgr = (isset($_POST['dropdownacctmgr']))? $_POST['dropdownacctmgr'] : '';
if ($dropdownacctmgr) {
...
$joined = date("Y-m-d h:i:s");
$dropdownacctmgr = (isset($_POST['dropdownacctmgr']))? $_POST['dropdownacctmgr'] : '';
if ($dropdownacctmgr) {
$sqlquery = "INSERT INTO testdiw VALUES('','".
$diwtitle ."','". $dropdownacctmgr ."','". $joined ."')";
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query!");
$sqlquery = "SELECT * from diw";
$sql = "SELECT acctmgr_name, acctmgr_phone, acctmgr_cellphone,
acctmgr_email FROM acctmgr WHERE acctmgr_name = '$dropdownacctmgr' ";
// execute your query
// process the result set
} else {
// do something if there was no value in the dropdownacctmgr variable
}
I have an error still,
Notice: Undefined variable: acctmgr_phone in c:\program files\easyphp1-7\www\diw3.php on line 37
and my code is as such:
<?PHP
$connect= mysql_connect("localhost","root","****")
or die("Could not connect to database in localhost!");
$result=mysql_select_db("test2diw")
or die("Could not select that database!");
$joined = date("Y-m-d h:i:s");
$dropdownacctmgr = (isset($_POST['dropdownacctmgr']))? $_POST['dropdownacctmgr'] : '';
if ($dropdownacctmgr) { $sqlquery = "INSERT INTO testdiw VALUES('','". $diwtitle ."','". $dropdownacctmgr ."','". $joined ."')";
$queryresult = mysql_query($sqlquery) or die(" Could not execute mysql query!");
$sqlquery = "SELECT * from diw";
$sql = "SELECT acctmgr_name, acctmgr_phone, acctmgr_cellphone, acctmgr_email FROM acctmgr WHERE acctmgr_name = '$dropdownacctmgr' ";
// execute your query
// process the result set
//} else { die("Could not select that database!")
// do something if there was no value in the dropdownacctmgr variable }
}
echo '<table border=1 align=center width=500>
<TR>
<TD colspan=2><b>General Deposition
Information</b></TD>
</TR>
<TR>
<TD><b>DIW Title:</b></TD>
<TD>' .$diwtitle. '</TD>
</TR>
<TR>
<TD>Account Manager Information:</TD> <TD>' .$dropdownacctmgr. '<BR>' .$acctmgr_phone. '</TD>
</TR>
</TABLE>';
?>
