Output problem

Dev Shed Forums Sponsor:

December 7th, 2012, 05:36 AM

chrismarsden

Registered User

Join Date: Dec 2012

Posts: 25

Time spent in forums: 8 h 36 m 44 sec
Reputation 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

December 7th, 2012, 06:11 AM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,130

Time spent in forums: 2 Months 2 Weeks 23 h 46 m 38 sec
Reputation Power: 1736

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

December 7th, 2012, 06:22 AM

chrismarsden

Registered User

Join Date: Dec 2012

Posts: 25

Time spent in forums: 8 h 36 m 44 sec
Reputation Power: 0

I cant get that to work...

December 7th, 2012, 06:27 AM

chrismarsden

Registered User

Join Date: Dec 2012

Posts: 25

Time spent in forums: 8 h 36 m 44 sec
Reputation 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.

December 7th, 2012, 06:32 AM

chrismarsden

Registered User

Join Date: Dec 2012

Posts: 25

Time spent in forums: 8 h 36 m 44 sec
Reputation 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

December 7th, 2012, 06:58 AM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,130

Time spent in forums: 2 Months 2 Weeks 23 h 46 m 38 sec
Reputation Power: 1736

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.

December 7th, 2012, 07:15 AM

chrismarsden

Registered User

Join Date: Dec 2012

Posts: 25

Time spent in forums: 8 h 36 m 44 sec
Reputation 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.

December 7th, 2012, 06:05 PM

MrFujin

Lord of the Dance

Join Date: Oct 2003

Posts: 3,130

Time spent in forums: 2 Months 2 Weeks 23 h 46 m 38 sec
Reputation Power: 1736

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:

Original - 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

 
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.

December 7th, 2012, 08:12 PM

Triple_Nothing

Contributing User

Join Date: Jun 2009

Posts: 297

Time spent in forums: 3 Days 8 h 45 m 39 sec
Reputation Power: 5

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.

#10

December 7th, 2012, 09:01 PM

Triple_Nothing

Contributing User

Join Date: Jun 2009

Posts: 297

Time spent in forums: 3 Days 8 h 45 m 39 sec
Reputation Power: 5

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.

#11

December 10th, 2012, 04:46 AM

chrismarsden

Registered User

Join Date: Dec 2012

Posts: 25

Time spent in forums: 8 h 36 m 44 sec
Reputation 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($result, MYSQL_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'];



}

 

?>

Developer Shed Advertisers and Affiliates