displaying data to a table and calculate

**ak1dnar** · Mar 26 '07, 01:52 PM

Please post your mySQL script here.

**DavidOwens** · Mar 26 '07, 02:08 PM

Originally posted by ajaxrand

Please post your mySQL script here.

not sure if this is what you mean

[php]
$result = mysql_query("SE LECT staffid, region, firstname, surname FROM users WHERE region='Pm'");

echo "<table border='1'>
<tr>
<th>Region ID</th>
<th>Firstname </th>
<th>Surname</th>
<th>Saturday</th>
<th>Monday</th>
<th>Tuesday</th>
<th>Wednesday </th>
<th>Thursday</th>
<th>Friday</th>
<th>Default</th>
</tr>";while($ro w = mysql_fetch_arr ay($result))
{
echo "<tr>";
echo "<td>" . $row['region'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['surname'] . "</td>";

$namequery = "SELECT region_id, name FROM stores WHERE region_id='REGI ON5'order by name ASC";

$result = mysql_query($na mequery) or die('Error, query failed');
$count = mysql_num_rows( $result);
if ($count > 0)
{
echo '<select name="item">';
while($result_r ow = mysql_fetch_arr ay($result))
{
echo '<option value="'.$resul t_row[0].'" >';
echo ($result_row[1]);

echo '</option>';
}
echo '</select>';
[/php]

so the above code, prints out the ID, firstname, surname etc from users where the region id is PM, then comes the drop down list, which does what i want, but just cant get it in the right place....and you know when u think about a problem to much ,u get the old headache and just cant think, that what i have got now.

pass me the vodka lol.

**ak1dnar** · Mar 26 '07, 02:55 PM

I asked about the mySQL script that you used to create the table.
if it is not available export it. from PhpMyadmin. and post it here

**DavidOwens** · Mar 26 '07, 03:00 PM

Originally posted by ajaxrand

I asked about the mySQL script that you used to create the table.
if it is not available export it. from PhpMyadmin. and post it here

-- Table "stores" DDL

CREATE TABLE `stores` (
`id` int(4) NOT NULL auto_increment,
`store_id` varchar(250) default NULL,
`name` varchar(250) default NULL,
`store_type_id` varchar(250) default NULL,
`region_id` varchar(250) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table "users" DDL

CREATE TABLE `users` (
`id` int(10) NOT NULL auto_increment,
`region` varchar(250) default NULL,
`staffid` varchar(250) default NULL,
`firstname` varchar(250) default NULL,
`surname` varchar(250) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

**DavidOwens** · Mar 26 '07, 07:15 PM

Originally posted by DavidOwens

-- Table "stores" DDL

CREATE TABLE `stores` (
`id` int(4) NOT NULL auto_increment,
`store_id` varchar(250) default NULL,
`name` varchar(250) default NULL,
`store_type_id` varchar(250) default NULL,
`region_id` varchar(250) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

-- Table "users" DDL

CREATE TABLE `users` (
`id` int(10) NOT NULL auto_increment,
`region` varchar(250) default NULL,
`staffid` varchar(250) default NULL,
`firstname` varchar(250) default NULL,
`surname` varchar(250) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

please guys...iv been searchin this problem for the past 2hrs and still nothing, lol looked through most of the documentation on the subject on this site.

if u look at the php code few posts up, you can see that i have a drop down and its currently displaying stores which match a certain region ID, but the drop down is displayin in the wrong place and i dont know how to get it in place, under the heading SATURDAY in the display tabel:(, once thats done my problem is sorted, PLEASE PLEASE guys, check out the code, and put it right, it not like i havent tried to sort the problem out, startin to get depressed now, cuz iv been searchin and looked through countless webpages and still nuffin.

please help.

**ak1dnar** · Mar 27 '07, 05:56 AM

Try to do the same to remaining days also by using this way.
[PHP]
<?php
$con = mysql_connect(" localhost","roo t","dba");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db ("test", $con);

$sql="SELECT id, staffid, region, firstname, surname FROM users WHERE region='region1 '";
$result=mysql_q uery($sql) or die("Error Occured while Searching Records : " . mysql_error());

echo "<table border='1'>
<tr>
<th>ID</th>
<th>Region ID</th>
<th>Staffid</th>
<th>Firstname </th>
<th>Surname</th>
<th>Saturday</th>
<th>Monday</th>
<th>Tuesday</th>
<th>Wednesday </th>
<th>Thursday</th>
<th>Friday</th>
<th>Default</th>
</tr>";

while($row = mysql_fetch_ass oc($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['region'] . "</td>";
echo "<td>" . $row['staffid'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['surname'] . "</td>";

$namequery = "SELECT name FROM stores ";
$result = mysql_query($na mequery) or die('Error, query failed');

echo '<td><select name="saturday" >';
while($result_r ow = mysql_fetch_arr ay($result))
{
echo '<option value="'.$resul t_row[0].'" >';
echo ($result_row[0]);
echo '</option>';
}
echo '</select></td>';

echo "</tr>";
}
echo "</table>";mysql_c lose($con);
?>

[/PHP]

**DavidOwens** · Mar 27 '07, 08:18 AM

Originally posted by ajaxrand

Try to do the same to remaining days also by using this way.
[PHP]
<?php
$con = mysql_connect(" localhost","roo t","dba");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db ("test", $con);

$sql="SELECT id, staffid, region, firstname, surname FROM users WHERE region='region1 '";
$result=mysql_q uery($sql) or die("Error Occured while Searching Records : " . mysql_error());

echo "<table border='1'>
<tr>
<th>ID</th>
<th>Region ID</th>
<th>Staffid</th>
<th>Firstname </th>
<th>Surname</th>
<th>Saturday</th>
<th>Monday</th>
<th>Tuesday</th>
<th>Wednesday </th>
<th>Thursday</th>
<th>Friday</th>
<th>Default</th>
</tr>";

while($row = mysql_fetch_ass oc($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['region'] . "</td>";
echo "<td>" . $row['staffid'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['surname'] . "</td>";

$namequery = "SELECT name FROM stores ";
$result = mysql_query($na mequery) or die('Error, query failed');

echo '<td><select name="saturday" >';
while($result_r ow = mysql_fetch_arr ay($result))
{
echo '<option value="'.$resul t_row[0].'" >';
echo ($result_row[0]);
echo '</option>';
}
echo '</select></td>';

echo "</tr>";
}
echo "</table>";mysql_c lose($con);
?>

[/PHP]

thanks mate, go that to work, lol all that time spent yesterday n its almost sorted in the space of a page view. you guys do a fantastic job, keep up the good work.

**ak1dnar** · Mar 27 '07, 08:23 AM

Its a pleasure to hear that. ;)

**DavidOwens** · Mar 27 '07, 09:05 AM

Originally posted by ajaxrand

Its a pleasure to hear that. ;)

kist noticed one last problem, is that its only displaying 1 record, but i would like it to display all the record that the query is asking.

so for example, if i was to query by region4, it should display 25 records, not just the one.

but im going to try sort this out on my own, ul hear back from me later, if iv had no success.

cheers guys.

ps. do you know were the problem lies???

**DavidOwens** · Mar 27 '07, 03:21 PM

Originally posted by DavidOwens

kist noticed one last problem, is that its only displaying 1 record, but i would like it to display all the record that the query is asking.

so for example, if i was to query by region4, it should display 25 records, not just the one.

but im going to try sort this out on my own, ul hear back from me later, if iv had no success.

cheers guys.

ps. do you know were the problem lies???

well guys its take this long and havent manageed to sort it :(, lol we are talkin a good 4hrs after i have done other things, oh da joys.

lol reaches for the orange juice(wishin it ws vodka).

**DavidOwens** · Mar 27 '07, 03:34 PM

Originally posted by DavidOwens

kist noticed one last problem, is that its only displaying 1 record, but i would like it to display all the record that the query is asking.

so for example, if i was to query by region4, it should display 25 records, not just the one.

but im going to try sort this out on my own, ul hear back from me later, if iv had no success.

cheers guys.

ps. do you know were the problem lies???

well guys its take this long and havent manageed to sort it :(, lol we are talkin a good 4hrs after i have done other things, oh da joys.

lol reaches for the orange juice(wishin it ws vodka).

**ronverdonk** · Mar 27 '07, 10:51 PM

The reason that it displays only 1 dropdown box in your code is that you use the $result variable, holding the resource ID of the query, in both queries, destroying the result from the first query. I have changed the resource ID variable of the second query to $result1, as you see in the following sample.

Also, but that will get you into problems later, you use the fixed name 'saturday' for all <select> drop down boxes. When posting the data you will only find one $_POST['saturday'] so you have to make that select name different for each box. I suggest adding an index to it, so they will be unique.

Also, in the following code snippet I added the create and inserts of both tables as a comment. I used that in my test. Good luck.

[php]
<?php
/*
create table users (id int, staffid int, region varchar(20), firstname varchar(20), surname varchar(20));
insert into users values(1,1,'reg ion1','John', 'Doe'),(2,2,'re gion1','Pete', 'Mackay');
create table stores (name varchar(10));
insert into stores values('Name1') ,('Name2'),('Na me3'),('Name4') ,('Name5'),('Na me6');
*/
$con = mysql_connect(" localhost","ron verdonk","ronni e09");
if (!$con) {
die('Could not connect: ' . mysql_error());
}
mysql_select_db ("vwso", $con);

$sql="SELECT id, staffid, region, firstname, surname FROM users WHERE region='region1 '";
$result=mysql_q uery($sql)
or die("Error Occured while Searching Records : " . mysql_error());
echo "<table border='1'>
<tr>
<th>ID</th>
<th>Region ID</th>
<th>Staffid</th>
<th>Firstname </th>
<th>Surname</th>
<th>Saturday</th>
<th>Monday</th>
<th>Tuesday</th>
<th>Wednesday </th>
<th>Thursday</th>
<th>Friday</th>
<th>Default</th>
</tr>";

while($row = mysql_fetch_ass oc($result)) {
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['region'] . "</td>";
echo "<td>" . $row['staffid'] . "</td>";
echo "<td>" . $row['firstname'] . "</td>";
echo "<td>" . $row['surname'] . "</td>";

$namequery = "SELECT name FROM stores ";
$result1 = mysql_query($na mequery)
or die('Error, query failed');

echo '<td><select name="saturday" >';
while($result_r ow = mysql_fetch_arr ay($result1)) {
echo '<option value="'.$resul t_row[0].'" >';
echo ($result_row[0]);
echo '</option>';
}
echo '</select></td>';
echo "</tr>";
}
echo "</table>";mysql_c lose($con);
?>[/php]

Ronald :cool:

**ak1dnar** · Mar 28 '07, 03:51 AM

I really missed that Ronald, sorry its my mistake.

**ronverdonk** · Mar 28 '07, 08:33 AM

No problem, can happen (and will happen one day) to anyone.

Ronald :cool:

displaying data to a table and calculate

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment