SitePoint Sponsor

User Tag List

Page 2 of 2 FirstFirst 12
Results 26 to 40 of 40
  1. #26
    SitePoint Enthusiast snarzom's Avatar
    Join Date
    Jan 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hi i still havnt got through this problem. Can some one shed some light on this. Please help.

  2. #27
    SitePoint Enthusiast snarzom's Avatar
    Join Date
    Jan 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    with this problem, what i need is for the code to display all rows with the postcode or postcodes that has been added to the database. so any results that have what ever the postcode are shown. the code only show if there is one postcode inputted, if i do two or more it has no results, no errors.

    what i need is for the results to show like.

    ID name postcode

    this would list all results, each person would be issued with 1 or more postcodes to view that have been assigned to them.

  3. #28
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,188
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    It is difficult to help you, when we do not know how your database table are setup. How do you store the postcode's in the db?

    If they are stored into one field only, then you could do:

    WHERE field_name IN (postcode, postcode, postcode)

    And then loop through the result set it returns.

  4. #29
    SitePoint Enthusiast snarzom's Avatar
    Join Date
    Jan 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    here is my query,
    Code:
    SELECT `user`.username, `user`.postcode, `form`.userID, `form`.added_by, `form`.email, `form`.phone, `form`.mobile, `form`.address, `form`.suburb, `form`.postcode, `form`.`state`, `form`.`datetime` FROM `user` inner join `form` on `user`.postcode = `form`.postcode WHERE `user`.username = '".$HTTP_SESSION_VARS['usersession']."'ORDER BY userID DESC";
    can i add a second where to do this? all info is stored in one DB table

  5. #30
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,188
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    I am not certain what the difference between the "user" and the "form" table is, it looks like both contain user data?

    Ive updated the query below, remember that the "postcode1, postcode2, postcode3" need to be replaced with the dynamic function you use to decide which postcodes to pull.

    Code:
    SELECT `user`.username, `user`.postcode, `form`.userID, `form`.added_by, `form`.email, `form`.phone, `form`.mobile, `form`.address, `form`.suburb, `form`.`state`, `form`.`datetime` FROM `user` inner join `form` on `user`.postcode = `form`.postcode WHERE `user`.username = '".$HTTP_SESSION_VARS['usersession']."' && `user`.postcode IN (postcode1, postcode2, postcode3) ORDER BY userID DESC";
    Ive also added a example on how to do it with mysql

    PHP Code:
    $postcodes = array(34563234562335633456); //etc

    $result mysql_query("SELECT `user`.username, `user`.postcode, `form`.userID, `form`.added_by, `form`.email, `form`.phone, `form`.mobile, `form`.address, `form`.suburb, `form`.`state`, `form`.`datetime` FROM `user` inner join `form` on `user`.postcode = `form`.postcode WHERE `user`.username = '".$_SESSION['usersession']."' && `user`.postcode IN (".implode(','$postcodes).") ORDER BY userID DESC")

    while (
    $buffer mysql_fetch_assoc($result))
        {
        echo 
    $buffer['userID'].' '.$buffer['username'].' '.$buffer['postcode'].'<br />';
        } 

  6. #31
    SitePoint Enthusiast snarzom's Avatar
    Join Date
    Jan 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    with the postcodes there is 2500 here in OZ, do i have to put all of them into a array or is there another way?
    If i email you the file can you fix?

  7. #32
    SitePoint Enthusiast snarzom's Avatar
    Join Date
    Jan 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    i tried the ,ysql one and got the following error - Parse error: parse error, unexpected T_WHILE in /home/httpd/vhosts/fURL.com/httpdocs/admin/default.php on line 180

    which is
    while ($buffer = mysql_fetch_assoc($result))

    the user contains all the admin user details. the form is the data the person filling out the form on the web fills in, this is then shown to the admin by postcode.

  8. #33
    SitePoint Enthusiast snarzom's Avatar
    Join Date
    Jan 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    have tried this as well ..

    Code:
    mysql_select_db($database_conn_fra, $conn_fra);
    $query_Recordset1 = " SELECT `user`.username, `user`.postcode, `form`.userID, `form`.added_by, `form`.email, `form`.phone, `form`.mobile, `form`.address, `form`.suburb, `form`.postcode, `form`.`state`, `form`.`datetime` FROM `user` inner join `form` on `user`.postcode = `form`.postcode WHERE `user`.username = '".$HTTP_SESSION_VARS['usersession']."'ORDER BY userID DESC LIMIT ".$startRow_Recordset1.", ".$maxRows_Recordset1."";
    $Recordset1 = mysql_query($query_Recordset1, $conn_fra) or die(mysql_error());
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
    no luck

  9. #34
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,188
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    The reason it fails with a parse error above, is due to the query is missing the ending ;

    What conserns me is why you need to check for 2500 postcodes at the same time? Why do you need to display that many at the same time?

    Please explain what exactly it is supposed to display and why.

  10. #35
    SitePoint Enthusiast snarzom's Avatar
    Join Date
    Jan 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    what i have is a web page with a from, in that postcode is the persons name, address, suburb and postcode all this is stored in a database.
    Then the main administrator will assign another person to administer people by postcode, so any person that lives in say 0800 that administrator can only see people that live in 0800.
    But some people may be assigned 2 or more post codes to administer like 0800, 0801, 0802. this is put into a single field in the master admin with there username and password.
    They view there postcodes decending with there name, date joined, postcode, then there is a link to vioew all there details.

  11. #36
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,188
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Ok, I think your problem is due to your database design.

    As I understand you have a database containing different persons. Each person are put under their postcode. Then you have administrators who should be able to moderate those persons depending on what postcodes they have been given. From one postcode to unlimited.

    Currently you store the postcodes into one field for each administrator, dividing them by a comma. While this works on smaller projects, it is a terrible solution to the problem.

    Instead you should create a linking table which will help you pull the correct data much simpler and faster from the database.

    Before the attachment get approved you can also view it from [url=http://test.kaizen-web.com/snarzom_example.png]here[url]

    I am not certain if you have a own postcode table containing all the possible postcodes your users can have. If you dont, just put the postcode directly on the address table instead (though that will not be that good a solution, since you dont have a master list of the possible postcodes a user can have).

    To pull the data from this database layout you can use a query similar to the one below. It would be a good idea to tune the query by adding indexes to any fields that would benefit from it. If you sort by the lastname for example, then it could benefit from an index.
    Code:
    SELECT user.*, address.*, code.* FROM admin_link as link, postcode as code, address as address, person as user WHERE link.admin_id=".(int) $admin_id." && link.postcode_id=code.postcode_id && code.postcode_id=address.postcode_id && address.person_id=user.person_id ORDER BY link.postcode_id ASC, user.person_lastname ASC
    If you have any questions, feel free to ask.
    Attached Images Attached Images

  12. #37
    SitePoint Enthusiast snarzom's Avatar
    Join Date
    Jan 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thank you for the help , this seems to be a bit out of my league, here is the wholle page code, can you redesign it if possible.

    Code:
    <?php
    // Buzz inet PHPLS03 - Check User Session is set
    session_start();
    if(!isset($HTTP_SESSION_VARS['usersession'])){
        header("Location: ../login.php");
     
    session_destroy();
     
    $message="";
    }
    ?><?php require_once('../Connections/conn_fra.php'); ?>
    <?php
    if (!function_exists("GetSQLValueString")) {
    function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
    {
      $theValue = get_magic_quotes_gpc() ? stripslashes($theValue) : $theValue;
     
      $theValue = function_exists("mysql_real_escape_string") ? mysql_real_escape_string($theValue) : mysql_escape_string($theValue);
     
      switch ($theType) {
        case "text":
          $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
          break;    
        case "long":
        case "int":
          $theValue = ($theValue != "") ? intval($theValue) : "NULL";
          break;
        case "double":
          $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
          break;
        case "date":
          $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
          break;
        case "defined":
          $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
          break;
      }
      return $theValue;
    }
    }
     
    $currentPage = $_SERVER["PHP_SELF"];
     
    $maxRows_Recordset1 = 30;
    $pageNum_Recordset1 = 0;
    if (isset($_GET['pageNum_Recordset1'])) {
      $pageNum_Recordset1 = $_GET['pageNum_Recordset1'];
    }
    $startRow_Recordset1 = $pageNum_Recordset1 * $maxRows_Recordset1;
     
    mysql_select_db($database_conn_fra, $conn_fra);
    $query_Recordset1 = " SELECT `user`.username, `user`.postcode, `form`.userID, `form`.added_by, `form`.email, `form`.phone, `form`.mobile, `form`.address, `form`.suburb, `form`.postcode, `form`.`state`, `form`.`datetime` FROM `user` inner join `form` on `user`.postcode = `form`.postcode WHERE `user`.username = '".$HTTP_SESSION_VARS['usersession']."'ORDER BY userID DESC LIMIT ".$startRow_Recordset1.", ".$maxRows_Recordset1.""; 
    $Recordset1 = mysql_query($query_limit_Recordset1, $conn_fra) or die(mysql_error());
    $row_Recordset1 = mysql_fetch_assoc($Recordset1);
     
    if (isset($_GET['totalRows_Recordset1'])) {
      $totalRows_Recordset1 = $_GET['totalRows_Recordset1'];
    } else {
      $all_Recordset1 = mysql_query($query_Recordset1);
      $totalRows_Recordset1 = mysql_num_rows($all_Recordset1);
    }
    $totalPages_Recordset1 = ceil($totalRows_Recordset1/$maxRows_Recordset1)-1;
     
    mysql_select_db($database_conn_fra, $conn_fra);
    $query_Recordset2 = "SELECT * FROM news";
    $Recordset2 = mysql_query($query_Recordset2, $conn_fra) or die(mysql_error());
    $row_Recordset2 = mysql_fetch_assoc($Recordset2);
    $totalRows_Recordset2 = mysql_num_rows($Recordset2);
     
    $queryString_Recordset1 = "";
    if (!empty($_SERVER['QUERY_STRING'])) {
      $params = explode("&", $_SERVER['QUERY_STRING']);
      $newParams = array();
      foreach ($params as $param) {
        if (stristr($param, "pageNum_Recordset1") == false && 
            stristr($param, "totalRows_Recordset1") == false) {
          array_push($newParams, $param);
        }
      }
      if (count($newParams) != 0) {
        $queryString_Recordset1 = "&" . htmlentities(implode("&", $newParams));
      }
    }
    $queryString_Recordset1 = sprintf("&totalRows_Recordset1=&#37;d%s", $totalRows_Recordset1, $queryString_Recordset1);
    ?> 
    i got the following error

  13. #38
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,188
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    That code is a mess.

    You have queries executing which is not used. You limit the total rowset query. etc.

    I actually have problems understand what exactly that code is supposed to do, you try to count the total rows in the frist query, then you select everything from the news table to get a count. But you dont use any of the results.

    I assume you are trying to modify someone elses code, I think you will be better off writing everything from the start. As the code is really messy, and very resource demanding compared to what it could have been.

    If you want to implent what I mentioned above, it means that you will need to redesign your code by adding that functionality. I.e. you need to change the entire script, not just a page of it.

    I would recommend that your read up on database relationships until you understand how they work.

  14. #39
    SitePoint Enthusiast snarzom's Avatar
    Join Date
    Jan 2007
    Posts
    64
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thank you, but i have done this with DW8 a few extensions, and the little knowledge i have on this. all this works but one part which is how do i split one bit of data and display certain results into a row.

  15. #40
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,188
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    The problem with your code, is that it is not doing what you want to archive according to your explanation a few posts earlier.

    If you are using DW with a few extentions to write the php code, that is most probably why it looks so messy. The main problem is that it does several things it did not need to do to archive the same result. This leads to a more resource demanding and slower application. Not to mention it can lead to major security issues.

    For your question, there are many ways to "split data". In general you need to know exactly what you want to display, and then limit the "search" so it only contain the data you want.

    Your main problem, is that you do not have a proper solution to "limit the search" yet. A possible solution to it, could be the method I mentioned in my earlier post.

    I understand that you are not used to php, which makes your task harder. But the only way to solve the problem is to get your hands dirty, read tutorials, reverse engineer scripts (find out why the author did as he did etc). In the end you will know enough to solve your issue.

    Good Luck


Bookmarks

Posting Permissions

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