Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 5 of 5
  1. #1
    New Coder
    Join Date
    Nov 2011
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Question How to show the data from mysql table for my case?

    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 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_query0) / $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>=&& $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.

  • #2
    Senior Coder
    Join Date
    Jan 2011
    Location
    Missouri
    Posts
    4,197
    Thanks
    23
    Thanked 605 Times in 604 Posts
    What you want to do is not hard you just have to limit this query:
    Code:
    $result = mysql_query("SELECT * FROM form");
    to return data only when the 'block' column contains the cityname that the logged in user has:
    Code:
    $result = mysql_query("SELECT * FROM form WHERE block = '$cityname'");
    You will have to change your
    Code:
    $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.

  • #3
    New Coder
    Join Date
    Nov 2011
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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 Code:
    <?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>";
    }

    ?>
    Last edited by coolcool; 11-18-2011 at 05:44 PM.

  • #4
    Regular Coder
    Join Date
    Oct 2009
    Location
    United States
    Posts
    157
    Thanks
    8
    Thanked 4 Times in 4 Posts
    PHP Code:
    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:

    PHP Code:
    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.
    Adobe Dreamweaver shall be destroyed!

  • Users who have thanked votter for this post:

    coolcool (11-19-2011)

  • #5
    New Coder
    Join Date
    Nov 2011
    Posts
    12
    Thanks
    1
    Thanked 0 Times in 0 Posts
    thanks now working


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •