Thread: Output problem

    #1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    25
    Rep Power
    0

    Output problem


    Hi, Im having a problem with outputing data from my table.

    I have several tables which i want to pull data from.

    One table is customer_info in which i have a customer id and 4 values assigned to this id.

    I want to output the values of the id on one line.

    the code i have so far is:

    Code:
    <?php
    $username="username is here";
    $password="password is here";
    $database="database name is here";
    
    mysql_connect(localhost,$username,$password);
    @mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM customers, customers_to_extra_fields";
    
    $result=mysql_query($query);
    
    $num=mysql_numrows($result);
    
    mysql_close();
    
    echo "<b><center>Database Output</center></b><br><br>";
    
    $i=0;
    while ($i < $num) {
    
    $customers_firstname=mysql_result($result,$i,"customers_firstname");
    $customers_lastname=mysql_result($result,$i,"customers_lastname");
    $customers_telephone=mysql_result($result,$i,"customers_telephone");
    $value=mysql_result($result,$i,"value");
    
    echo "<b>$customers_firstname
    $customers_lastname</b><br>$customers_telephone<br>$value<hr><br>";
    
    
    
    $i++;
    }
    
    ?>
    the output i get is

    Database Output

    Christopher Marsden
    075465465456
    0ut atim3
    Christopher Marsden
    075465465456
    DeLorean
    Christopher Marsden
    075465465456
    DMC-12
    Christopher Marsden
    075465465456
    Silver

    what i actually want is

    Christopher Marsden
    075465465456
    0ut atim3, DeLorean, DMC-12, Silver

    then when a second user is input this shows below
  2. #2
  3. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,609
    Rep Power
    1945
    I suggest you take a look at the group_concat function

    In you query, you haven't specified the condition on how the two tables should be linked together, so will have to replace "something" with the correct field name: (query is not tested)
    Code:
    SELECT c.customers_firstname, c.customers_lastname, c.customers_telephone,  group_concat(cf.value)
    FROM customers c
    INNER JOIN customers_to_extra_fields cf ON c.something = cf.something
    GROUP BY customers_firstname, customers_lastname, customers_telephone
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    25
    Rep Power
    0
    I cant get that to work...
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    25
    Rep Power
    0
    just had a bit of a play with it using link provided... considering im a bit noobish with this i really am struggling...

    any help much appreciated.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    25
    Rep Power
    0
    just to be clear its not tables i want to join, its all records for customer id to show as single outputs.

    eg

    Customer id Value
    1 hello
    1 Im
    1 really
    1 stuck


    shows as hello, Im, really, stuck
  10. #6
  11. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,609
    Rep Power
    1945
    Ok, I assumed the field 'value' was from the "extra_fields" table.
    When you have issues with tables, it is important you tell us which how you have structured your table(s) and field(s).

    Remove the reference to the second table:
    Code:
    SELECT c.customers_firstname, c.customers_lastname, c.customers_telephone,  group_concat(c.value) AS value
    FROM customers c
    GROUP BY customers_firstname, customers_lastname, customers_telephone
    I also forgot to add the alias for the group_concat.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    25
    Rep Power
    0
    Table : Customers
    Fields required to report: customers_firstname, customers_lastname, customers_phonenumber

    Table : customers_to_extra_fields
    Fields required to report: customer_id, value

    The problem i keep coming across is that customer id is entered in to the database 4 times as 4 values are posted for that id.

    this gives 4 seperate records when reporting:

    Christopher Marsden
    075465465456
    0ut atim3
    Christopher Marsden
    075465465456
    DeLorean
    Christopher Marsden
    075465465456
    DMC-12
    Christopher Marsden
    075465465456
    Silver

    all i want to show is
    christopher marsden
    07564654564
    value 1 assigned to user id, value 2 assigned to user id, value 3 assigned to user id, value 4 assigned to user id.

    im probably not explaining this very well.
  14. #8
  15. Lord of the Dance
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Oct 2003
    Posts
    3,609
    Rep Power
    1945
    If you need to get fields from two tables, then you do need to join them in order to map the values to the correct name.

    Do you have a customer_id in you Customers table? then this will probably be a Primary key.
    The customer_id in the second table will then be a foreign key, which is used to refer to the customer table.

    In the query in my first reply, you will have to replace "something" with the correct name: "customer_id".
    (untested)
    sql Code:
     
    SELECT c.customers_firstname, c.customers_lastname, c.customers_telephone,  group_concat(cf.VALUE) AS VALUE
    FROM customers c
    INNER JOIN customers_to_extra_fields cf ON c.customer_id = cf.customer_id
    GROUP BY customers_firstname, customers_lastname, customers_telephone


    Try work a bit with the query if it does not return the expected value.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    676
    Rep Power
    7
    I'm assuming the `customers_to_extra_fields.customer_id` is to match a `Customers.customer_id` value? And just in case your goal is to do something SIMILAR to a phonebook, then maybe run 2 different quiries, one within a while() loop of another. First one will make the steps through your customers, and the inner loop will echo the details of that particular customer.
  18. #10
  19. No Profile Picture
    Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2009
    Posts
    676
    Rep Power
    7
    Would something similar to this work for your goal? Outter loop prints the customer. Inner loop adds his details. If/Then prints "Details: detail(1)" to start and prints ", detail(...)" on remainder.

    PHP Code:
    <?php
      $customers 
    mysqli_query($con,"SELECT customers_firstname, customers_lastname, customers_telephone FROM `customers`;");
      
    $details mysqli_query($con,"SELECT customer_id, value FROM `customers_to_extra_fields`;");
      while(
    $cust mysqli_fetch_assoc($customers)) {
        echo 
    "\nName: " $cust['customers_firstname'] . " " $cust['customers.lastname'];
        echo 
    "\nPhone: " $cust['customers_phonenumber'];
        
    $rows mysqli_num_rows($details);
        
    $i 1;
        while((
    $det mysqli_fetch_assoc($details)) && $i <= $rows) {
          if(
    $i == 1) {
            echo 
    "\nDetails: " $det['value'];
          } else {
            echo 
    ", " $det['value'];
          }
        }
      }
    ?>
    Few things missing from this. The inner while() won't print correct values until a WHERE is added to the $details query. You will need to add the customer id column to the $customers query. You then need to alter the script a bit so each loop will ask for a different WHERE value in the $details query for its id column, so it selects only that customer's items.

    I'm not too sure your page formatting, but it may be in your interest to also place a <br /> by all the \n items.


    *
    Last edited by Triple_Nothing; December 7th, 2012 at 09:12 PM.
  20. #11
  21. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2012
    Posts
    25
    Rep Power
    0

    solution


    I managed to fix this problem, for anyone who is stuck in the future i post this as a solution.

    PHP Code:
                                                                         
    <table border="1" width="50%" cellpadding="1" cellspacing="1">                                                            
                                                 
    <?php
    $username
    ="";
    $password="";
    $database="";
     
    mysql_connect(localhost,$username,$password);
    @
    mysql_select_db($database) or die( "Unable to select database");
    $query="SELECT * FROM orders, customers AS c INNER JOIN customers_to_extra_fields AS e ON c.customers_id = e.customers_id";
     
     
    $result=mysql_query($query);
     
    mysql_close();
     
    echo 
    "<b><center>Database Output</center></b><br><br>";
     
    $resultarray = array();
    while (
    $row mysql_fetch_array($resultMYSQL_ASSOC)) {
        
    $rowarray = array();
        foreach (
    $row as $key => $value) {
            
    $rowarray[$key] = $value;
        }
        
    $resultarray[] = $rowarray;
    }

    $id 0;
    foreach (
    $resultarray as $row) {
     if(
    $row['customers_id'] != $id){
     echo 
    "<tr>";
      echo 
    "<td><b>".$row["customers_firstname"]."</b></td>";
      
      echo 
    "<td><b>".$row["customers_lastname"]."</b><br></td>";
      
      echo 
    "<td>".$row["customers_telephone"]."</td>";
       echo 
    "<td>".$row["date_purchased"]."</td>";
     }
     echo 
    "<td>".$row["value"]."";
     
    $id $row['customers_id'];

    }
     
    ?>

IMN logo majestic logo threadwatch logo seochat tools logo