posting and reading from database (multiple tables)

typical_finnish

Hello everybody

I have database with multiple tables.
some example tables from my database:

product_groups
|--id
|--code
|--name
|--description
product_states
|--id
|--code
|--name
|--description
products
|--id
|--product_group_id
|--code
|--name
|--description
|--price
|--product_state_id

Now i'm currently trying to do two things. Write to database with some data from different tables and read form database using data from many tables.

Here is the reading part of the code.'

<?php
//connection
$yhteys = mysql_connect("localhost", "user", "pass") or die("Yhdistäminen ei onnistunut!");

//database
mysql_select_db("prosesso_eskoo", $yhteys) or die("Tietokantaa ei löytynyt!");


$query="SELECT *  FROM products p
JOIN product_groups pg ON (p.product_group_id = pg.id)";

$result=mysql_query($query);

$num=mysql_numrows($result);

mysql_close();

echo "<b><center>Database print:</center></b><br><br>";

$i=0;
while ($i < $num) {

$id=mysql_result($result,$i,"p.id");
$product_group_id=mysql_result($result,$i,"pg.name")
$code=mysql_result($result,$i,"p.code");
$name=mysql_result($result,$i,"p.name");
$description=mysql_result($result,$i,"p.description");
$price=mysql_result($result,$i,"p.price");
$product_state_id=mysql_result($result,$i,"p.product_state_id");

echo "<b>ID:$id </b><br> <b>Ruokaryhma:$product_group_id</b><br>Koodi: $code<br>Nimi: $name<br>Kuvaus: $description<br>Hinta: $price<br>Tila: $product_state_id<br><hr><br>";

$i++;
}

?>

Now what it should do, is get all the rows from products table and use product_group tables id to get the name to product groups name. and use name from product_state table to use in products state

Sorry if my question is somewhat confusing, but my english is not yet very good.

Krik

I had a hard time seeing what you were trying to do so I copied out your code to my editor and started tweaking and debugging and here is what I had when I got done.

<?php
//connection
$yhteys = mysql_connect("localhost", "user", "pass") or die("Yhdistäminen ei onnistunut!");

//database
mysql_select_db("prosesso_eskoo", $yhteys) or die("Tietokantaa ei löytynyt!");

echo '<b><center>Database print:</center></b><br><br>';

$query = "
	SELECT *
	FROM products
	LEFT JOIN
	(
		SELECT id AS pgid, name AS pgname
		FROM product_groups 
	)PG ON PG.pgid = products.product_group_id
	LEFT JOIN
	(
		SELECT id AS psid, name AS psname
		FROM product_states 
	)PS ON PS.psid = products.product_state_id
	";

$result = mysql_query($query);

while ($row = mysql_fetch_array($result))
{
	echo '<b>ID: ' . $row['id'] . '</b><br>
	<b>Ruokaryhma: ' . $row['pgname'] . '</b><br>
	Koodi: ' . $row['code'] . '<br>
	Nimi: ' . $row['name'] . '<br>
	Kuvaus: ' . $row['description'] . '<br>
	Hinta: ' . $row['price'] . '<br>
	Tila: ' . $row['psname'] . '<br>
	<br>';
}
?>

Well, that is mostly what I had. I had made a few other personal preference changes that I though might end up confusing you so I switched them back to your method. The most notable change is the use of "LEFT JOIN" in the query. Then how the data from each row is called in the while loop. The change to the while loop really isn't part of the solution outside of it showing how you can use each rows column data.

I hope its mostly self explanatory. For myself explaining it all seemed a lot more complicated than just fixing it.

typical_finnish

Thanks for that.

I have hard time to understan array, so i dont use it 🙂 But someday i will get it and then i use it all the time🙂

Can i ask the next question here in the same thread.

If and when i fetch all the data from that table (producs) and assosiate all data to variables like this:
[php code]
$code=mysql_result($result,$i,"p.code");
$name=mysql_result($result,$i,"p.name");
[/code]

can i use it in form like this

<form id="form" name="form" method="post" action="food-order-database.php">
<?php
echo '<label> insert quantity of' . $name . '</label> <input type="text" name="food_quantity" id="food_quantity" />';
?>
</form>

and then in food-order-database-php something that stores products (food in this case) name and quantity in database... (not yet know how).

So the question 1 is, am i correct, can it done that way?
and question 2 is, is that also easier with array, and if how 🙂 (i still dont know how to use array)

Derokorian

Note:

$code=mysql_result($result,$i,"p.code");
$name=mysql_result($result,$i,"p.name");

$code will then be equal to $row['code'] as in Krik's response.
$name will then be equal to $row['name'] as in Krik's response.

So you can then do echo $row['code']; instead of $code = mysql_result($result,$i,"p.code"); echo $code; so its much simpler and more compact.

bradgrafelman

typical_finnish;10994434 wrote:
question 1 is, am i correct, can it done that way?

Not really; take a look at the information you'd be POST'ing to 'food-order-database.php' and you'll find that you're missing an important piece.

When your example form is submitted, the $_POST array will look something like:

Array
(
	[food_quantity] => 3
)

Now, based on that information, can you tell me which product this quantity is referring to?

One way to maintain the relationship of quantity to product would be to include some identifying value (such as an AUTO_INCREMENT value from the D😎 in the field name itself, e.g. you'd have HTML form inputs like:

<input type="text" name="food_quantity[123]">

which, when submitted, would result in the $_POST array looking more like:

Array
(
	[food_quantity] => Array
		(
			[123] => 3
		)
)

which is much more beneficial, since you can now relate product id #123 to the entered quantity of '3'.

typical_finnish;10994434 wrote:
question 2 is, is that also easier with array, and if how 🙂 (i still dont know how to use array)

I believe I already answered both questions (and, if not: "yes" and "see above"), but I'd still like to address that last statement you've made (twice now).

Why on earth are you working with relational databases and user input when you haven't learned one of the basic variable types - not only in PHP, but in programming in general? I would reference the adage about 'learn to crawl before you walk,' but I don't think even that description does your current situation justice (it'd be more like 'learn to crawl before entering the Olympic skating competitions').

Krik

If and when i fetch all the data from that table (producs) and assosiate all data to variables like this:
[php code]
$code=mysql_result($result,$i,"p.code");
$name=mysql_result($result,$i,"p.name");
[/code]

I wanted to make some comments about using the data that way. When I first started working with databases (many years ago) I used "list" to convert all the items in the $row array to variables. What caused me to change was two fold. First, if that "list" was 20 or 30 items long (large tables, or several joins) it took a lot of time to type, and of course that could lead to typos that caused errors that would make you go crazy trying to find. Second was if any major change was done to the database, and if I had 10 queries to the database, I had 10 lists to change. And what was really annoying about that was if only 1 or 2 queries were even going to use the new database columns.

Also, while minor, it is worth noting that loops are one of the things that can slow down code. Now if you only have a small number of items in the database you would be hard pressed to do too much to effect your codes speed. But I see this is a "products" database and that means it could become huge. So less lines of code and php to process in that while loop could in some cases give a noticeable performance boost.

So in short the method I used in that while loop was to save you a ton of hassle down the road.

And, as already said, arrays are so fundamental to any programing you really should take some time to learn them. Php.net I am sure has several [man]array[/man] examples that could help and there are tons of tutorials around as well. It's maybe at most a day (likely an hour) of your time but it could save you weeks if not months (cumulatively) of headaches. They really are little more than a fancy list. They are very handy if you should need to pass, for example, 100 variables (exaggerated for emphasis) to a function, you could pass then in one variable instead of having to type out all 100.

bradgrafelman wrote:
(it'd be more like 'learn to crawl before entering the Olympic skating competitions').

Something like that. I hardly remember when I learn arrays, I think I would be hard press to find any code I have written without one, and I keep everything.

Weedpacket

Krik wrote:
And, as already said, arrays are so fundamental to any programing you really should take some time to learn them.

As I tend to say - something along the lines of "learning how to use arrays is like learning how to count past 1."

Derokorian

//WITHOUT ARRAYS
$user1 = 'Weedpacket';
$user2 = 'Krik';
$user3 = 'Derokorian';
$user4 = 'bradgrafelman';
$user5 = 'typical_finnish';

echo 'Users in this thread:<br>'
   .$user1.'<br>'
   .$user2.'<br>'
   .$user3.'<br>'
   .$user4.'<br>'
   .$user5.'<br>';

// WITH ARRAYS
$users = array('Weedpacket','Krik','Derokorian','bradgrafelman','typical_finnish');
echo 'Users in this thread:<br>';
foreach( $users as $user ) {
   echo $user.'<br>';
}