...

View Full Version : How to show the data from mysql table for my case?



coolcool
11-18-2011, 11:28 AM
i have3 Table

block table
id,block
(total 9 blocks based on cityname ex:london,mumbai....)

users table
id,username,password,block,access
(this is registration form fields.Select your block from drop down menu)

total 3 acess levels
0 for admin,1 for editor and 2 for regular user

form table
id,block,scheme,status. (Select your block from drop down menu)

i have a working login registration system also successfully get the results from the Form table.

My code

<?php
include '../include/functions.php';

if(!loggedin()) {
header("Location: login.php");
exit ();
}

$per_page = 3;
$pages_query = mysql_query("SELECT COUNT('id') FROM form");
$pages = ceil(mysql_result($pages_query, 0) / $per_page);

$page = (isset ($_GET['page'])) ? (int)$_GET['page'] : 1;
$start = ($page - 1) * $per_page;

$result = mysql_query("SELECT * FROM form");

echo "<table>
<tr>
<th>ID</th>
<th>Block</th>
<th>SCHEME NAME</th>
<th>Status</th>
</tr>";
while($row = mysql_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['id'] . "</td>";
echo "<td>" . $row['block'] . "</td>";
echo "<td>" . $row['scheme'] . "</td>";
echo "<td>" . $row['status'] . "</td>";


echo "</tr>";
}
echo "</table>";
echo "Page: ";
if($pages>=1 && $page<=$pages) {
for($x=1; $x<=$pages; $x++) {

echo ($x == $page) ? '<strong><a href="?page='.$x.'">'.$x.'</a></strong> ' : '<a href="?page='.$x.'">'.$x.'</a> ' ;
}
}

?>

But i need if a user is from london block then show only london block results of Form table and if from mumbai show only mumbai results of Form table.

Please help friends.

sunfighter
11-18-2011, 04:47 PM
What you want to do is not hard you just have to limit this query:

$result = mysql_query("SELECT * FROM form");
to return data only when the 'block' column contains the cityname that the logged in user has:

$result = mysql_query("SELECT * FROM form WHERE block = '$cityname'");
You will have to change your

$pages_query = mysql_query("SELECT COUNT('id') FROM form");
and add the WHERE block = '$cityname' to it also.


But I do have some observation and a question.
Question: How do you relate info in one table to another? Is it by ID?

I have to guess here, When a user registers they are placed into the 'users table' and the ID is generated as a primary unique number. But the ID in 'block table' and 'form table' is the same ID number and therefore not unique?

An observation: All three tables have the ID and block info. If that ID is the same in all tables then 'block table' is not needed, it is redundant; and placing block info into the 'users table' is also. You can free up some room in that table by dropping the column.

coolcool
11-18-2011, 05:12 PM
Thanks for your reply.
block table is predefined (id is primary)
form table is filled by registered user.(id is primary)(block is a drop down menu and get data from block table)

Below is my code and but it only returns one row.

<?php
include '../include/functions.php';

if(!loggedin()) {
header("Location: login.php");
exit ();
}
$user = $_SESSION['username'];
$queryget = mysql_query("SELECT block FROM users WHERE username='$user'") or die("Query is not working");
$row = mysql_fetch_assoc($queryget);

$block = $row['block'];

if($row)
{

$result = mysql_query("SELECT * FROM form WHERE block='$block'");
$row1 = mysql_fetch_array($result);
echo "<table>
<tr>
<th>ID</th>
<th>Block</th>
<th>SCHEME NAME</th>
<th>Status</th>
</tr>";

echo "<tr>";
echo "<td>" . $row1['id'] . "</td>";
echo "<td>" . $row1['block'] . "</td>";
echo "<td>" . $row1['scheme'] . "</td>";
echo "<td>" . $row1['status'] . "</td>";
echo "</tr>";
echo "</table>";
}

?>

votter
11-19-2011, 03:50 PM
if($row)
{

$result = mysql_query("SELECT * FROM form WHERE block='$block'");
$row1 = mysql_fetch_array($result);
echo "<table>
<tr>
<th>ID</th>
<th>Block</th>
<th>SCHEME NAME</th>
<th>Status</th>
</tr>";

echo "<tr>";
echo "<td>" . $row1['id'] . "</td>";
echo "<td>" . $row1['block'] . "</td>";
echo "<td>" . $row1['scheme'] . "</td>";
echo "<td>" . $row1['status'] . "</td>";
echo "</tr>";
echo "</table>";
}


That will only get one row because you aren't looping through the results. You need a while loop in there like this:



if($row)
{

$result = mysql_query("SELECT * FROM form WHERE block='$block'");
while($row1 = mysql_fetch_array($result))
{
echo "<table>
<tr>
<th>ID</th>
<th>Block</th>
<th>SCHEME NAME</th>
<th>Status</th>
</tr>";

echo "<tr>";
echo "<td>" . $row1['id'] . "</td>";
echo "<td>" . $row1['block'] . "</td>";
echo "<td>" . $row1['scheme'] . "</td>";
echo "<td>" . $row1['status'] . "</td>";
echo "</tr>";
echo "</table>";
}
}


I'd also change mysql_fetch_array to mysql_fetch_assoc since it does not look like you need the indexed array set.

coolcool
11-19-2011, 05:04 PM
thanks now working



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum