Databases
StackOverflow2013
Postsdbo
POReading information from two tables into two forms at once
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POReading information from two tables into two forms at once
    text
    copied!<p>For my first attempt at programming, I am making a simple webapp with php and MySQL, to enter customer information and store information about orders.</p> <p>So far I've made a form to enter customer information, a page to edit that information and another page to show the different customers, which is all working fine.</p> <p>I've run into a stump when trying to implement the next stage however.</p> <p>I have an editcustomers page with 3 columns. </p> <ul> <li>Column 1 shows a form containing customer information, allowing it to be edited.</li> <li>Column 2 allows ordering of products and showing how many products were ordered</li> <li>Column 3 shows the total paid so far, and the total owing.</li> </ul> <p>The code for the page I have at present:</p> <pre><code>&lt;html&gt; &lt;?php $id = $_GET['id']; require_once('connect.php'); $sth = $dbh-&gt;query("SELECT * FROM customers where id = '$id'"); $sth-&gt;setFetchMode(PDO::FETCH_ASSOC); ?&gt; &lt;div id="main"&gt; &lt;div id="left"&gt; &lt;form name="myForm" action ="process.php" method ="post" &gt; &lt;?php while($row = $sth-&gt;fetch()){ ?&gt; &lt;p&gt;&lt;input type = "hidden" name ="id" value="&lt;?php echo $row["id"] ?&gt;"/&gt; &lt;p&gt;&lt;input type = "text" name ="firstName" size ="30" value=" &lt;?php echo $row["firstName"]?&gt;"/&gt; &lt;p&gt;&lt;input type = "text" name ="lastName" size ="30" value="&lt;?php echo $row["lastName"]?&gt;"/&gt; &lt;p&gt;&lt;/p&gt; &lt;input type="submit" value="Update" /&gt; &lt;?php }?&gt; &lt;/div&gt; &lt;div id="mid"&gt; &lt;p&gt;Amount owed&lt;br /&gt; &lt;input type = "text" name ="moneyOwed" size ="30" /&gt;&lt;/p&gt; &lt;input type="submit" value="Pay" /&gt; &lt;p&gt;Number of aaaa&lt;br /&gt; &lt;input type = "text" name ="numAaa" size ="30" /&gt;&lt;/p&gt; &lt;p&gt;Number of bbbb&lt;br /&gt; &lt;input type = "text" name ="numBbb" size ="30" /&gt;&lt;/p&gt; &lt;p&gt; &lt;input type="submit" value="Update" /&gt; &lt;/div&gt; &lt;div id="right"&gt; &lt;b&gt;Total Balance&lt;/b&gt; &lt;p&gt; Money owed: &lt;/p&gt; &lt;p&gt; aaa total: &lt;/p&gt; &lt;p&gt; bbb total: &lt;/p&gt; &lt;p&gt; Total: &lt;/p&gt; &lt;input type = "text" name ="pay" size ="20" /&gt;&lt;/p&gt; &lt;input type="submit" value="Make Payment" /&gt; &lt;/div&gt; &lt;?php $dbh =null; ?&gt; &lt;/body&gt; &lt;/html&gt; </code></pre> <p>I have the following database structure:</p> <ul> <li>A customers table, basic information like firstname, lastname, etc along with an id field.</li> <li>A products table, listing the different products, only 1 row containing the costs for each product.</li> <li>A purchases table, with an id field , fields like numProductA, numProductB, showing the quantity of each product ordered</li> </ul> <p>The problem I'm having is that in my editcustomerspage, I have a database query to read in information from the customers table to fill the fields in the form in my first column and a separate query to update it if that if the function chosen.</p> <p>Updating the second formshould be OK, as I could use a hidden field to differentiate forms, however I am unsure how to read the information in from the Customers table and from the Purchases table so that I can populate the fields in my second form..</p> <p>Part of the problem is that the purchases table may be empty for a given customer, if that customer has not yet placed an order.</p> <p>I was considering getting rid of the purchases table, and tacking the fields onto the users table which would solve my problem, although I think that is generally considered bad practice?</p> <p>The products will be fixed in the application, and are not going to change. Even so, would it still be better to have column names cost and name and each products as a record? </p> <p>What are some approaches I could take to solving this problem?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload