PHP MySql select from two tables and order

[gilgil2]

Hi

I have two Mysql tables, one with the information in and another with payment information in. I want to echo the results that have paid first and then the rest of the results. If information has no payment associated with it it does not appear in the payment table.

Does anyone have any suggestions for the best way to do this? I know it would be much easier to combine the two tables but I coildn't get that to work with PayPal IPN so have set it up this way instead.

Any suggestions/examples would be greatly appreciated.

Thanks

[the_traveller]

Hi

it sould be some thing like this

SELECT * FROM payment INNER JOIN information ON (information.informationID = payment.paymentID) WHERE information.info != '' ORDER BY paymentID

[diafol]

If you could show the structure of both tables, that would be useful - we need to look for the 'common field' so we can join them (probably via INNER JOIN).

[gilgil2]

Thanks for the replies. The common field is 'itemid' in the payments table and 'name' in the information table.

[diafol]

Like this?

SELECT `info`.*,`payments`.* FROM `info` INNER JOIN `payments`ON `info`.`name`= `payments`.`item_id`

[gilgil2]

Hi thanks a lot for the help, sorry I have been slow getting back to it. This works well now but I am struggling with where to put the search part in,.

$result = mysql_query("SELECT * FROM info WHERE name LIKE '%".$_POST['name']."%'");

I was using this to search for results, and want to echo the results that correspond to that query and also appear in both tables using the join, where do I put this search part?

Thanks in advance

[diafol]

where do I put this search part?

At the end, but you need to specify the table:

SELECT `info`.*,`payments`.* FROM `info` INNER JOIN `payments`ON `info`.`name`= `payments`.`item_id` WHERE `info`.`name` LIKE '%{$_POST['name']}%'

HOWEVER - NEVER, EVER place an unclean variable into the SQL. Treat it with mysql_real_escape_string() first.

[gilgil2]

Thanks, I am just getting everything set up and then going to read up on mysql escape before it goes live.