kirk112

Found the answer. I have to use either (simplified) SELECT j.job_id FROM jobs AS j WHERE j.job_title IN (100,101,102,103) AND j.job_id NOT IN (SELECT job_id FROM jbe_jobs_sent WHERE jbe_id = '3') ORDER BY ja.added_on or SELECT j.job_id FROM jobs AS j LEFT JOIN jbe_jobs_sent AS jbes ON jbes.job_id = j.job_id AND jbes.jbe_id = 3 WHERE j.job_title IN (100,101,102,103) AND jbes.job_id IS NULL Is one way better that the other in terms of performance? Thanks again!!

Hi I have to following query SELECT j.job_id, jt.job_title, s.salary, lg.location_name, ls.specific_name, LEFT(jat.advert_text,1000) AS advert, DATE_FORMAT(ja.added_on, '%d-%m-%Y') AS date_added FROM jobs AS j LEFT JOIN job_titles AS jt ON jt.job_id = j.job_title LEFT JOIN salary AS s ON s.salary_id = j.salary LEFT JOIN location_general AS lg ON lg.location_id = j.location LEFT JOIN location_specific AS ls ON ls.specific_id = j.location_specific LEFT JOIN job_adverts AS ja ON j.job_id = ja.job_id LEFT JOIN job_adverts_text AS jat ON ja.advert_text_id = jat.advert_text_id WHERE j.job_title IN (100,101,102,103) AND ja.advert_live = 1 ORDER BY ja.added_on This selects all the jobs that have matched the query and works fine, but I also have the following table jbe_jobs_sent sent_id (PK) job_id (FK) for the jobs table jbe_id (ID) for the jobs by email What I need to do is join this table to the about query so that it only selects the rows where the jbe_jobs_sent.job_id do not match the j.job_id I have tried LEFT JOIN jbe_jobs_sent AS jbes ON jbes.job_id != j.job_id AND jbes.jbe_id = 3 but this joins against all the other records in the jbe_jobs_sent table Hope this make sense Thanks for your help.

Try MATCH(name,dance) AGAINST Not sure if it makes a difference.

Hi I currently have the following table structure. jbe_messages jbe_message_id jbe_id candidate_ref message opened clicked date_sent jbe_settings jbe_id candidate_ref jbe_settings active date_created This structure stores all the search information in a serialized string in the jbe_settings field and the contents of the message in the jbe_messages->message. Which works fine, but I am trying to think of a way in which I can track all of the different jobs that have been sent to a specific candidate (tracked by job_id which is the primary key of the jobs table) so that each time the script runs it does no send the same job to the same candidate more than once. I have thought about having a extra table Jbe_sent_jobs Jbe_sent_id Jbe_message_id Job_id And each of the job_id that are sent are stored in this table, the problem with this is that this table would become very large very quickly i.e 5000 different emails, each with 40 jobs (200000 records from the first send out and the emails are normally sent twice per week). Can anyone think of a better way of storing this information. Thanks

Hi I have one php file that creates and prints out an xml file. What I am trying to do is open the php file (from a seperate script) and get the xml content so that I can manipulate it. But when ever I use fopen, get_file_contents etc all I get is the php script and not the xml content Hope this explains better. Cheers

no if I use echo I get <?php header('Content-Type: text/xml'); header('Content-Disposition: inline; filename=sample.xml'); include_once('page_config.inc.php'); $vars = get_defined_vars(); echo "<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>"; echo "<root>\n"; foreach($vars['js_type'] as $key => $value) { $v = 'js_type_'.$key; echo "<js_config>\n"; echo "<array_key>$key</array_key>\n"; echo "<header>{$vars[$v]}</header>\n"; echo "</js_config>"; } echo "</root>\n"; ?> Thanks for your help - really can't get my head around this

*bump*

** Bump ** tried everything I can think of, there must be a simple way of doing this if i use $file = file_get_contents("../includes/page_config_xml.inc.php"); print nl2br(htmlentities($file)); I get <?php header('Content-Type: text/xml'); header('Content-Disposition: inline; filename=sample.xml'); include_once('page_config.inc.php'); $vars = get_defined_vars(); print "<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>"; print "<root>\n"; foreach($vars['js_type'] as $key => $value) { $v = 'js_type_'.$key; print "<js_config>\n"; print "<array_key>$key</array_key>\n"; print "<header>{$vars[$v]}</header>\n"; print "</js_config>"; } print "</root>\n"; ?> instead of the xml only AHHHHHHHHHHHHh

http://www.swiftmailer.org/ Swift is a fully OOP library for sending e-mails from PHP websites and applications. It does not rely on PHP's native mail() function which is known for using high server resources when sending multiple emails. Instead, Swift communicates directly with an SMTP server or a MTA binary to send mail quickly and efficiently.

Where you added $sql = mysql_query($sql); change to $sql = mysql_query($sql) or die(mysql_error()); this will tell you if there are any problems with the sql query

I have a script which creates and xml file using the code below. How can I pase this xml file using php. I am currently using xml_parse($xml_parser,file_get_contents($file)); but this get the php code and not the out putted xml. I can't get my head around how to parse the outputted xml using php <?php header('Content-Type: text/xml'); header('Content-Disposition: inline; filename=sample.xml'); include_once('page_config.inc.php'); $vars = get_defined_vars(); print "<?xml version=\"1.0\" encoding=\"iso-8859-1\"?>"; print "<root>\n"; foreach($vars['js_type'] as $key => $value) { $v = 'js_type_'.$key; print "<js_config>\n"; print "<array_key>$key</array_key>\n"; print "<header>{$vars[$v]}</header>\n"; print "</js_config>"; } print "</root>\n"; ?> This give me the following xml <?xml version="1.0" encoding="iso-8859-1" ?> - <root> - <js_config> <array_key>1</array_key> <header>ALL JAVASCRIPT FILES</header> </js_config> - <js_config> <array_key>2</array_key> <header>TABS JAVASCRIPT FILES</header> </js_config> - <js_config> <array_key>3</array_key> <header>STANDARD JAVASCRIPT FILES</header> </js_config> </root> Thanks for your help!

Take a look at WITH ROLLUP function this might be what you are looking for http://dev.mysql.com/doc/refman/4.1/en/group-by-modifiers.html this will give you an extra row with all the values in the coloum added up. If you do not need to loop through each record after the query has run you could use SUM((fin_record_debit-fin_record_credit)) as fin_record_bal1 Hope this points you in the right direction

Not tested this but this might work? SELECT event_id, SUM( IF( subMemberID IS NULL, 1, 0 ) ) FROM EVENTS AS e LEFT JOIN notAttending AS n ON e.event_id = n.eventID GROUP BY event_id Let us know how you get on

Hi fenway, I have attached a screen shot of this, I really don't understand why a NULL row is returned every time, if I run select * it returns an empty data set. [attachment deleted by admin]

Everytime there is no match I still get a NULL row returned????

Hi Fenway, thanks for your response explained a lot. Just one more thing when using group_concat it always retrieves one row even if the where does not match any fields, I can over come this by using 'having length' but this seems a little bit messy, just wondering if there is a better way to achieve this? SELECT CONCAT('\'',GROUP_CONCAT(DISTINCT(ls_id), '\'' SEPARATOR ',\'')) AS locations FROM ip_locations AS ip WHERE ip.ip_code = 'jkj' HAVING length(locations) > 0 Again thanks for your time

No idea why this works but if I change the original query to SELECT ls_id, CONCAT('\'', GROUP_CONCAT(DISTINCT(ls_id), '\'' SEPARATOR ',\'')) AS locations FROM ip_locations AS ip LEFT JOIN location_specific AS ls ON ls.specific_id = ip.ls_id WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls_id it works perfect, but a soon as I remove the first concat, it reverts back to giving me all the results as a blob. Any ideas why?? Thanks Barand for your help, got myself mixed up with the fields that I wanted to select, should be able to work it out after I have gotten some sleep. Thanks again everyone!

Hi Barand I have removed the GROUP BY ls_id and I am still getting the same incorrect results I have been running this query in phpMyAdmin. If I run this query and replace GROUP_CONCAT(ls_id) to a varchar field (instead of an smallint) it run correctly, does anyone have any idea on where to start debugging this I have been going around in circles for days Thanks!

I have taken it all the way back to SELECT GROUP_CONCAT(ls_id) FROM ip_locations AS ip WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls_id And I am still getting the same blob result

Hi effigy If I take out the distinct altogether I still have the same problem, I.E. ls_id locations 1 [bLOB - 10 Bytes] 2 [bLOB - 2 Bytes] Cheers

Hi, I am trying to group fields together, which I have managed using GROUP_CONCAT, but when I concat on a SMALLINT datatype the results is return as ls_id locations 1 [bLOB - 2 Bytes] 2 [bLOB - 2 Bytes] instead of ls_id locations 1 '1', '2' 2 '1', '3' This is the query that I have been using. SELECT ls_id, GROUP_CONCAT(DISTINCT(ls_id), '\'' SEPARATOR ',\'') AS locations FROM ip_locations AS ip LEFT JOIN location_specific AS ls ON ls.specific_id = ip.ls_id WHERE ip.ip_code IN ('A1', 'A2', 'A3', 'A4') GROUP BY ls_id Does anyone know where I am going wrong? Thanks for your help!

Hello Just wondering if anyone can point me in the right direction I am current working on a site on which we would like to display relevant information to the users based on their geographical location, which we are hoping to get for their IP address. Does anyone know where I can get an IP database which is broken down to UK city level, or anything simlar that would give us the same results. Thanks for your help

The p1, p2, py are alias for the tables, you will need to use them if you want to join the same table twice. py is the reference for the players table. so the sql would be... SELECT py.player_id, p1.position AS position1, p2.position AS position2 FROM players AS py LEFT JOIN positions AS p1 ON py.position1 = p1.position_id LEFT JOIN positions AS p2 ON py.position2 = p2.position_id

how do you join the events table to the concerts table? If its on event_id then the following should work SELECT * FROM events AS e LEFT JOIN concerts AS c ON e.event_id = c.event_id ORDER BY c.type

Is this what you where after? SELECT u1.username AS sender, u2.username AS reciver FROM mail AS m LEFT JOIN users AS u1 ON m.senderid = u1.user_id LEFT JOIN users AS u2 ON m.reciver = u2.user_id