[SOLVED] Repeat results

[confused_aswell]

Hi

 

I have got this problem where when I query 1 table the results come out fine, but when I try to query 2 tables that's when the problem starts. I have seven rows in each table, and when I try to query them both I get First Name, Last Name, Email Address in the top line of my excel file, then I get 7 results of the name Phillip below First Name then 7 results of the name Bloggs below Last Name and each email address for each entry in the second table next to the name Phillip Bloggs. I hope that makes sence?

 

Anyway, would someone mind taking a look at my script for obvious errors please.

 

Thanks,

 

Phil

 

Here is the script -

 

<?php
$host = 'localhost';
$user = 'username';
$pass = 'password';
$db = 'database';
$table = 'zen_address_book,zen_customers';
$file = 'export';

$link = mysql_connect($host, $user, $pass) or die("Can not connect." . mysql_error());
mysql_select_db($db) or die("Can not connect.");

$replace = array( 
   'entry_firstname' => 'First Name', 
   'entry_lastname' => 'Last Name',
   'customers_email_address' => 'Email Address'
); 

$values = mysql_query("SELECT entry_firstname, entry_lastname, customers_email_address FROM ".$table."");
$i=0;
while ($rowr = mysql_fetch_assoc($values)) {
if($i==0) {
foreach(array_keys($rowr) as $title)
$csv_output .= '"'.str_replace(array_keys($replace), $replace, $title).'",';
$csv_output .= "\n";	
}

foreach ($rowr as $key => $value) {
$csv_output .= '"'.$value.'",'; 

}
$csv_output .= "\n";
$i++;
}

$filename = $file."_".date("Y-m-d_H-i",time());
header("Content-type: application/vnd.ms-excel");
header("Content-disposition: csv" . date("Y-m-d") . ".csv");
header( "Content-disposition: filename=".$filename.".csv");
print $csv_output;
exit;

?>

[discomatt]

Read into JOINs

[Minase]

try replacing

 

if($i==0) {
foreach(array_keys($rowr) as $title)
$csv_output .= '"'.str_replace(array_keys($replace), $replace, $title).'",';
$csv_output .= "\n";	
}

 

with

 

foreach(array_keys($rowr) as $title) {
$csv_output .= '"'.str_replace(array_keys($replace), $replace, $title).'",';
$csv_output .= "\n";	
}

[confused_aswell]

Hi

 

Thanks for the reply, but the page does not load - it trying to connect all the time.

 

Thanks,

 

Phil

[Minase]

explain me better what you want to do,you did tell me that the page is returning you just same result 7 times or what?

[confused_aswell]

Hi

 

I think it is best that you have a look at the results, here is the www.phcleaning.co.uk/database2.php

 

I obviously want just 1 entry on each line that is called from the 2 tables.

 

Thanks,

 

Phil

[Minase]

ah lol =))

i though your script returned same result everytime.

dont worry your code has nothing,just open the file with wordpad or another text viewer instead of notepad  ;D

[confused_aswell]

Hi

 

That doesn't make much sense, I want it to open in a excel file. Do you know how to fix the problem?

 

Thanks,

 

Phil

[Minase]

never worked with excel but i think this will work

 

replace all \n with \r\n

[discomatt]

Or you could just ignore my suggestion.

[Minase]

i did understand something like you second time,but he doesnt need that....i think

he just need to make new line apear into excel file,but im wondering how your page work with that 2 tables...

 

[confused_aswell]

Hi

 

It doesn't have seemed to have worked, you can take a look for yourself on the above url.

 

Thanks,

 

Phil

 

PS I haven't ignored you, I am just following up the easiest answer first.

[discomatt]

No, he's getting repeating entries. This happens when you try to combine results from multiple tables in a single query without JOINing them properly.

 

PS I haven't ignored you, I am just following up the easiest answer first.

 

Understandable, but to be honest, you're just beating around the bush. Test your query in phpMyAdmin, you'll see that it's not a PHP issue.

[confused_aswell]

Discomatt

 

Can you suggest any good web tutorials for this joining?

 

Thanks,

 

Phil

[discomatt]

http://www.tizag.com/mysqlTutorial/mysqlleftjoin.php

 

I can help further if you show me an example query that you're trying to perform ( in plain text, no variables ) and your database structure ( or their relationship )

[Minase]

weird never thinked about 2 table in that way ...

now all i got from this thread is a headache..

pls make a sumary of everything you need to acomplish.

[confused_aswell]

Hi

 

I have got two tables. The first one has got entry_firstname,entry_lastname,entry_address,entry_suburb,entry_city,entry_postcode and the second table has got customers_email_address,customers_telephone.

 

So I obviously want to join them and have the results print out in the excel file.

 

It will show the results from both tables but I have got this repeating problem in the url above, so, is there anything you can help with please.

 

Thanks,

 

Phil

[confused_aswell]

Hi

 

I am sure I responded to this already, but it seems to have disappeared!

 

Anyway I have two tables in the database the first one stores the names and addresses and the other stores the email and telephone numbers. I have found a sql query that works in Dreamweaver but because the code in the script requires FROM ".$table.""); I can't use the query. Here is the query -

 

SELECT zen_address_book.entry_firstname, zen_address_book.entry_lastname, zen_address_book.entry_street_address, zen_address_book.entry_suburb, zen_address_book.entry_city, zen_address_book.entry_postcode, zen_customers.customers_email_address FROM zen_address_book, zen_customers WHERE zen_customers.customers_id = zen_address_book.customers_id

 

Unless we can rewrite the script some how, any ideas?

 

Thanks,

 

Phil

[discomatt]

Give this a shot

 

SELECT
a.entry_firstname, a.entry_lastname, a.entry_street_address, a.entry_suburb,
a.entry_city, a.entry_postcode, c.customers_email_address
FROM
zen_address_book as a
LEFT JOIN
zen_customers as c
ON
c.customers_id = a.customers_id

[confused_aswell]

Hi

 

Thanks for the reply. The problem I will have is in the script it's self. How can I insert that query when the last part of the query must be FROM ".$table."");

 

Is there a way of getting round this?

 

Thanks,

 

Phil

[discomatt]

Aren't you query'ing from multiple tables? What is the expected value of $table?

[confused_aswell]

Hi

 

I am not sure what you mean, but all I am saying is because of this line

$table = 'zen_address_book,zen_customers';

then we need to change the line

FROM ".$table."");

in the query. Because the

FROM ".$table."");

is calling from the tables in

$table = 'zen_address_book,zen_customers';

Does that make sense or I am barking up the wrong tree.

 

Thanks,

 

Phil

[confused_aswell]

Ha Ha

 

Done it. I just deleted out the FROM ".$table."

 

Anyway, it works, thank you so much for all your help, I will now go and play with it somemore.

 

Thanks,

 

Phil

[discomatt]

Glad we could get it solved.