List:MySQL and PHP« Previous MessageNext Message »
From:Richard Date:August 2 2007 7:44pm
Subject:Re: mysql extract propblem with my php script
View as plain text  
Hi thanks for your help, I've found the solution directly by changing 
the sql query :

SELECT a.message,a.date,b.surname,b.christianname,b.title
FROM messages a
JOIN memberinfo b
ON a.from=b.membercode WHERE a.id='$code'";

Works like a charm ( thanks to a member on mysql@stripped :)) !

=(SC)= Admin a écrit :
> I am new to this too.  But what works with me when querying 2 tables 
> using specific data is something slightly different to your method.
>
> Here's an example...
>
> $query = "SELECT * FROM `tickets`";
> $result = mysql_query($query) or die('Query failed. ' . mysql_error());
>
> while ($row = mysql_fetch_array($result)){
>
> $ticket_number = $row['ticket_number'];
>
> $query = "SELECT * FROM `messages` WHERE ticket_number = 
> `$ticket_number`;
>
> $result = mysql_query($query) or die('Query failed. ' . mysql_error());
>
> while($row = mysql_fetch_array($result)){
>
> echo $row['title'];
>
> echo $row['christian'];
>
> echo $row['surname'];
>
>
>
> I hope this helps you in some way.  As I said, this method works for 
> me when outputting specific information.
>
> Regards
>
> Paul.
>
>
>
> Arena Servers - Web Hosting
> http://www.arenasmithster.co.uk
> ----- Original Message ----- From: "Richard" <mysqlandphp@stripped>
> To: <php@stripped>
> Sent: Thursday, August 02, 2007 5:02 PM
> Subject: mysql extract propblem with my php script
>
>
>> Hello everyone, I'm new to this list, I hope I'm doing this correctly.
>>
>> I'm in the process of programming a ticket support system for my 
>> members.
>>
>> I have made two tables, one called "tickets" containing the tickets 
>> information :
>>
>> ticket number, ticket subject, ticket status, and member id of sender 
>> and date
>>
>> and the second table called messages containing :
>>
>> ticket number, message, sender and date
>>
>> This way I can have a list of all the tickets with a link to a page 
>> called viewticket to which I pass on the ticket number.
>>
>> On this page I connect to the database, select the database do the 
>> query and in a while do a fetch_array to create the table listing the 
>> messages which have the specified ticket number.
>>
>> And now here is the problem :
>>
>> the while + fetch array gives me for each line of the table:
>>
>> message, sender id, date
>>
>> And I do not want to show the sender id but the surname and christian 
>> name of the sender which are in a table called memberinfo
>>
>> So I've tried to do another query inside the while but I get now answer.
>>
>> Here is my code :
>>
>>  <?php
>>  require_once('config.php');
>>  require_once('db.class.php');
>>  require_once('session.inc.php');
>>  require_once('check.session.php');
>>
>>  //create new instance of class database
>>  $t1 = new ClassDataBase();
>>  //Connecting to database
>>  $t1->Connect(db_server,db_user,db_passwd);
>>  //selecting database
>>  $t1->Select(db_name);
>>  //getting message code
>>  $code = $_GET['code'] ;
>>  //mysql search string
>>  $sql = "SELECT * FROM messages WHERE id LIKE '$code'";
>>  $t1->Query($sql);
>>  ?>
>>  <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
>> <"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
>>  <html xmlns="http://www.w3.org/1999/xhtml">
>>  <head>
>>  <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
>>  <title>Message number <?php echo "$code" ?></title>
>>  </head>
>>
>>  <body>
>>  <fieldset>
>>  <legend>Message number <?php echo "$code" ?></legend>
>>  <?php
>>  while ($result = $t1->FetchArray()) {
>>  $from = $result['from'];
>>  //connection a second time to database
>>  //creating a second instance of database class
>>  $t2 = new ClassDataBase();
>>  //connecting to database
>>  $t2->Connect(db_server,db_user,db_passwd);
>>  //second search string
>>  $sql2 = "SELECT * FROM memberinfo WHERE membercode LIKE '$from'";
>>  //Select database for 2nd time
>>  $t2->Select(db_name);
>>  $t2->Query($sql2);
>>  $result2 = $t2->FetchArray();
>>  echo "<fieldset>\n"
>>  ."<legend>Message from .$result2['title']."  ".$result2['surname']." 
>> ".$result2['christianname']." sent on the 
>> ".$result['date'].".</legend>\n"
>>  ."<p> ".$result['message']." </p>\n"
>>  ."</fieldset>\n";
>>  }
>>  ?>
>>  </fieldset>
>>  </body>
>>  </html>
>>
>> Sorry I don't know how to send code on a mailing list. I can send you 
>> the classes if you need them but I think the code is quite explicit.
>>
>> As you can see I have tempted to connect a second time to the 
>> database, this is probably not the best way and does not even work 
>> but shows you what I want to do.
>>
>> How should I go about getting round this problem?
>>
>> Thanks in advance,
>>
>> Richard
>>
>> -- 
>> MySQL PHP Mailing List
>> For list archives: http://lists.mysql.com/php
>> To unsubscribe: http://lists.mysql.com/php?unsub=1
>>
>

Thread
mysql extract propblem with my php scriptRichard2 Aug
Re: mysql extract propblem with my php scriptRichard2 Aug