Help with array/mysql/download 1

[iranor]

Hi , I have a script that , when you execute it , it select data in mysql , and allow you to download the .txt file after (it isn't database table or other.)


I have two tables :

Mail , that contains : id , author , subject , content and date.
Mail_link contains : id , player_id , message_id and read.

When I execute , I need to select every message_id in Mail_link that is owned by player_id, and then store in an array.

After , I have another query that will scan the table Mail
to find every message (id) called by message_id.



The code is like this :


header("Content-Type: text/x-delimtext; name=Sauvegarde de courrier.txt");
header("Content-disposition: attachment; filename=Sauvegarde de courrier.txt");
echo "#\n";
echo "# Sauvegarde du courrier\n";
echo "#

http://www.bahagon.com\n";

echo "#\n# DATE : " . gmdate("d-m-Y H:i:s", time()) . " GMT\n";
echo "#\n";

mysql_connect($serveur,$nom,$passe) or die('MySQL error!');
mysql_select_db($base) or die('MySQL error!');

$select[0] = "SELECT message_id FROM mail_link WHERE player_id='$pid'";
$request[0] = mysql_query($select[0]);
$res = mysql_fetch_array($request[0]);
$num_results = mysql_num_rows($request[0]);

foreach( $res as $key){
$select[1] = "SELECT author, subject, date, content FROM mail WHERE id =$key";
$request[1] = mysql_query($select[1]);
$i=0;
while($results = mysql_fetch_array($request[1])) {
$i++;
echo "Auteur : ".$results['author']."\n";
echo "Sujet : ".$results['subject']."\n";
echo "Date : ".$results['date']."\n";
echo "Message : ".$results['content']."\n\n\n\n";
}

}

But it only shows 2 times the same query , even if there's only one or only 3.

Thanks for your help.

 

[tweenerz]

First of all, $res will only contain 1 element - message_id, so looping through it is meaningless. $key will always be message_id returned from the first query. I am assuming you are only expecting one row back from the first query since you didn't loop through that result set.

But it only shows 2 times the same query

Not sure what this means. But you should print out as many rows where the id in the mail table is equal to the message_id returned from the first query.

I hope that this makes sense to you.

 

[iranor]

In the query , there are multiple message_id owned by player_id. I want the script to take every message_id there , create an arrray , and after table Mail take every id in the array and print every results where id is equal message_id.

These are my mysql tables :



CREATE TABLE `mail` (
`id` tinyint(20) NOT NULL auto_increment,
`author` varchar(30) NOT NULL default '',
`subject` varchar(50) NOT NULL default '',
`content` text NOT NULL,
`date` date NOT NULL default '0000-00-00',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

#
# Content of table `mail`
#

INSERT INTO `mail` VALUES (1, 'Neomaster', 'Salut', 'content\ncontent\nsalut\n', '2004-01-02');
INSERT INTO `mail` VALUES (2, 'Susers', 'Testins', 'HelWoeld!\n<tes>T', '0000-00-00');
INSERT INTO `mail` VALUES (3, 'Hellp', 'World!!!', 'steslts', '0000-00-00');
INSERT INTO `mail` VALUES (4, 'Test', 'Testind', ' testingh', '0000-00-04');

CREATE TABLE `mail_link` (
`id` tinyint(20) NOT NULL auto_increment,
`player_id` smallint(50) NOT NULL default '0',
`message_id` smallint(50) NOT NULL default '0',
`read` char(1) NOT NULL default '',
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=5 ;

INSERT INTO `mail_link` VALUES (1, 1, 1, '0');
INSERT INTO `mail_link` VALUES (2, 2, 2, '0');
INSERT INTO `mail_link` VALUES (3, 1, 3, '0');
INSERT INTO `mail_link` VALUES (4, 1, 4, '0');

If I example take player_id=1 , there's 3 messages owned by him. When I download the file , only two output are printed in the file.

 

[tweenerz]

The quickest solution is to make two loops - one nested in the other. If you are worried about performance, there is probably another way of doing it where there aren't so many MySQL requests going on.

But the first thing you need to do since

there are multiple message_id owned by player_id

is loop through the result set. You are currently not doing that. You are simply taking the first row and throwing it into $res and looping through that (which only contains one element - message_id)

try changing

$res = mysql_fetch_array($request[0]);

to

while($res = mysql_fetch_array($request[0])){

and then put your closing } at the end.

also get rid of or move the following out of the loop you are going to create for performance reasons:

$num_results = mysql_num_rows($request[0]);

 

[tweenerz]

Also, get rid of the foreach loop, since $res only has one element. No need to loop. The loop you are looking for is the while loop you are going to create.

Also, by the code you have supplied, I don't know why you are getting 2 results being printed. You should only receive 1 - from the mail table.

 

[iranor]

Now , the file looks like this :

$pid="1";
header("Content-Type: text/x-delimtext; name=Sauvegarde de courrier.txt");
header("Content-disposition: attachment; filename=Sauvegarde de courrier.txt");
echo "#\n";
echo "# Sauvegarde du courrier\n";
echo "#

http://www.bahagon.com\n";

echo "#\n# DATE : " . gmdate("d-m-Y H:i:s", time()) . " GMT\n";
echo "#\n";
mysql_connect($serveur,$nom,$passe) or die('MySQL error!');
mysql_select_db($base) or die('MySQL error!');
$select[0] = "SELECT message_id FROM mail_link WHERE player_id='$pid'";
$request[0] = mysql_query($select[0]);
while($res = mysql_fetch_array($request[0])){
$select[1] = "SELECT * FROM mail WHERE id =$res";
$request[1] = mysql_query($select[1]);

echo "Auteur : ".$results['author']."\n";
echo "Sujet : ".$results['subject']."\n";
echo "Date : ".$results['date']."\n";
echo "Message : ".$results['content']."\n\n\n\n";

}

And the output

#
# Sauvegarde du courrier
#

http://www.bahagon.com

#
# DATE : 17-06-2004 19:56:59 GMT
#
Auteur :
Sujet :
Date :
Message :



Auteur :
Sujet :
Date :
Message :



Auteur :
Sujet :
Date :
Message :


BTW , The $res result = Array...

 

[tweenerz]

sorry. Change

$select[1] = "SELECT * FROM mail WHERE id =$res";

to

$select[1] = "SELECT * FROM mail WHERE id =$res[0]";

Also, you took out the inner while loop. You still need to loop through the inner result set.

So the nested loop structure should be this:

while($res = mysql_fetch_array($request[0])){
        $select[1] = "SELECT * FROM mail WHERE id =$res[0]";
        $request[1] = mysql_query($select[1]);
        while($results = mysql_fetch_array($request[1])) {   
                echo    "Auteur : ".$results['author']."\n";
                echo    "Sujet : ".$results['subject']."\n";
                echo    "Date : ".$results['date']."\n";
                echo    "Message : ".$results['content']."\n\n\n\n";
        }                
}

Here is my attempt at the explanation:

Every time you do a query, you are returned an array of arrays: an array of rows ($request[0] or $request[1]) each containing an array of fields ($res or $results). Since you only selected message_id in yuor first query, you are still getting an array, but it only has 1 field. So trying to print $res simply prints Array, but printing $res[0] prints the first (and, in this case, only) element.

Hope that helps

 

[iranor]

Thanks!! everything works fine now!

 

[iranor]

I want to do some changes here.

The code actually is :

header("Content-Type: text/x-delimtext; name=Sauvegarde de courrier.txt");
header("Content-disposition: attachment; filename=Sauvegarde de courrier.txt");
echo "#\n";
echo "# Sauvegarde du courrier\n";
echo "#

http://www.bahagon.com\n";

echo "#\n# Effectué le : " . date("Y/n/d g:i:s A") . "\n";
echo "#-------------------------------------------------------\n\n\n";
mysql_connect($serveur,$nom,$passe) or die('MySQL error!');
mysql_select_db($base) or die('MySQL error!');
$select[0] = "SELECT message_id FROM mail_link WHERE player_id='$pid'";
$request[0] = mysql_query($select[0]);
while($res = mysql_fetch_array($request[0])){
$select[1] = "SELECT * FROM mail WHERE id =$res[0]";
$request[1] = mysql_query($select[1]);
$results[1] = mysql_fetch_array($request[1]);
$results[2] = mysql_fetch_array(mysql_query("SELECT username FROM players WHERE player_id ='".$results[1]['author']."'"));

echo "Auteur : ".$results[2]['username']."\n";
echo "Sujet : ".$results[1]['subject']."\n";
echo "Date : ".$results[1]['date']."\n";
echo "Message : ".$results[1]['content']."\n\n\n\n";
}

How can I add another field in the download file to display 0 if the message isn't read and 1 if it is read? The read field is in the mail_link table.

I want the field to looks like this :

echo "Auteur : ".$results[2]['username']."\n";
echo "Read : ".$results[]['read']."\n";
echo "Sujet : ".$results[1]['subject']."\n";
echo "Date : ".$results[1]['date']."\n";
echo "Message : ".$results[1]['content']."\n\n\n\n";

How can I do that? I tried many thing but still don't works.