0

Hoping for some help with a sql query. I have the following:

$sql = "SELECT * from 
(SELECT m1.post_id as ID,post_date,m1.meta_value as project_name, m2.meta_value as access_key, m3.meta_value as seq_number
 FROM wpha_posts
 join wpha_postmeta m1 on m1.post_id = ID and m1.meta_key = '_field_5'
 join wpha_postmeta m2 on m2.post_id = ID and m2.meta_key = '_field_7'
join wpha_postmeta m3 on m3.post_id = ID and m3.meta_key = '_seq_num'
 where post_type = 'nf_sub' AND post_author = ".$profile_id." AND wpha_postmeta.seq_number = ".$seqNumber.") as v1";

$results = $wpdb->get_results($sql);

What I am trying to do is show data that is from two sql tables but only show results where the sequence number matches the number I have passed via the url.

$seqNumber = (int)$_GET['id'];

I am failing at matching with the meta value of the _seq_num meta key in the database.

Any help would be much appreciated

mischiefbec
  • 51
  • 1
  • 8
  • What does $sql look like before you get the results? I'd bet that $seqNumber isn't getting populated. or populated with the value you expect. then take that $sql output and run it in the database manually to see what SQL error you have. also... in your where clause: what tables are those limits being imposed upon? what table is post_Type and post_author coming from wpha_posts? Lastly are you sure you have data matching your criteria? – xQbert May 02 '18 at 14:27
  • I have an echo of the $seqNumber on the front end which says 33 which matches the _seq_num meta value in the database for that post – mischiefbec May 02 '18 at 14:29
  • So what does $SQL look like before it's executed? To me it looks like wpha_posts.ID relates to postmeta.post_ID and postMeta is a M:1 with wpha_posts. looks like it shoudl work. unless wpha_postmeta.seq_number isn't a numeric data type. Are you getting an error on the get_results? are you getting any results? it's why I would like to see the $SQL and have that run against the DB directly. If no error there then it's a communication error with the database and not an SQL/syntax one. Could it be that field_5, field_7 & seq_num don't all exist for an ID? thus an inner join eliminates it – xQbert May 02 '18 at 15:51

0 Answers0