How to get the value from column 4 in a table where column 3 matches a value and column 4 matches a value ?

Question

This is an example of a table from a Gravity form which has been put into a database (Mysql)

What Im doing is catching certain values from a submitted form and then comparing those values against another forms previously submitted values. I have managed to grab the values from the submitted form using gform_after_submission hook, its jsut the grabbing the value from the other forms database after matching.

Table layout

 --------------------------------------------------
 /  Lead_id / Form_id /Field_number/   value  /
 /-----------------------------------------------
 /   3     /    4    /     2       / email@email.com
 ---------------------------------------------------
 /   4     /    4    /     4      /    name mcname
 --------------------------------------------------
 /   5     /    4    /     7      /    Skype
 --------------------------------------------------
 /   6     /    3   /      2      / email2@email2.com
 -----------------------------------------------------
 /   7     /    3   /      4      /   name2 mcname2 
 -----------------------------------------------------
 /   8     /    3   /      7      /    TELEPHONE
 ----------------------------------------------------

So i have the value of "skype" And Im checking to see if the value skype is in the table and if so then I want to grab the data from the value where Field_number = 2 (the email address)

But Im having difficulty grabbing this value.

Here is where Ive ended up

$ContactMethods = "Skype";
$feildNum = 2;
$getEmailmentor = (float) $feildNum;

$SQL = "SELECT * FROM wp_rg_lead_detail WHERE field_number = '$getEmailmentor' AND           $ContactMethods = value";

 $result = mysql_query($SQL);

 while($row = mysql_fetch_array($result)) {
  // OUTPUT EMAIL
echo $row['value']."<b/>";
 }

The idea is to grab the email and then send an email to a match by $ContactMethods

I'm not great at MYSQL queries and would appreciate the help, Ive tried googling different ways to do this but I seem to be going around in circles.

subquerry, inner join or less bat shit crazy table design - pick one — , May 26 '14 at 22:17
It is a very odd way of constructing a table, its from a WP plugin. — Steven Mcsorley, May 26 '14 at 23:12
So to clarify, you want to get the row that has Skype, grab the `form_id` there, then use that to get field 2 (email) of the same `form_id`? — Alex.Ritna, May 26 '14 at 23:39
$SQL = "SELECT value FROM wp_rg_lead_detail WHERE field_number= '2' UNION SELECT value FROM wp_rg_lead_detail WHERE value = 'Skype'"; Think this is what I may be looking for derived from http://stackoverflow.com/a/14796825/1466347 — Steven Mcsorley, May 27 '14 at 00:21
Not working its just pulling all emails that have field_number = 2 — Steven Mcsorley, May 27 '14 at 00:50

score 1 · Answer 1 · answered May 27 '14 at 08:15

1

It seems you need relation between field_number and value by some means.
From your table input I can see that form_id can be used.

If so, you can use following solution:

select c.* from contacts c
 left join contacts t on c.form_id=t.form_id
  where c.field_number=? -- $getEmailmentor
    and t.value=? -- $ContactMethods
;

Bind the placeholders with valid values using prepare statement.

MySQL Fiddle Demo

answered May 27 '14 at 08:15

Ravinder Reddy

23,692
6
52
82

Wow looking good, how would I echo or print out the result please ? Tried $myInfo = mysql_result($result, 0, 'value'); echo $myInfo; – Steven Mcsorley May 27 '14 at 08:58

How to get the value from column 4 in a table where column 3 matches a value and column 4 matches a value ?

1 Answers1