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

Recommended Answers

All 7 Replies

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

Member Avatar for 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).

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

Member Avatar for diafol

Like this?

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

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

Member Avatar for 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.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.