passing MYSQL vars (NOT PHP vars) to PHP query

Web IT
Web IT used Ask the Experts™
1) I have a mysql query which works fine in PHP my admin .
2) The reuslt is a table with first and last name .
3) I am not allowed to create new tables so have all the info in the meta_data.
4) I wrote a mysql query which does what I want ,
5) In PHP I am able to run the queries with test vars (meaining without the mysql vars).

6 )The Question :
How do I pass in one variable a mysql query with multiple rows.
7) my php code loop which works with single sql statmens is(this is just the loop part as code is large:
(note this code works as it's requesting a set id).
$mysqli;
	/* check connection */
$getTodaysEventsFromLastPostID ="SELECT * FROM wp_postmeta WHERE post_id =267";
	if (mysqli_connect_errno()) {
		printf("Connect failed: %s\n", mysqli_connect_error());
		exit();
	}
	$QueryVar1= $mysqli->query("SELECT @post_id:= post_id FROM wp_postmeta WHERE meta_key LIKE 'etimeclockwp-in%'");
	$QueryVar2 =$mysqli->query("SELECT @uid:= meta_value FROM wp_postmeta WHERE  meta_key ='uid';");
	$totalQuery=$mysqli->query("SELECT  @post_id,@uid;");
	$query = $getTodaysEventsFromLastPostID ;
	$result = $mysqli->query($query);

	while($row = $result->fetch_array())
	{
		$rows[] = $row;
		//print_r($row);
	}

	foreach($rows as $row)
	{
		echo $row['0']."<br>";
	}

	/* free result set */
	$result->close();

	/* close connection */
	$mysqli->close();

Open in new window




7 )Pure mysql query is:

SELECT @surname:= meta_value FROM wp_postmeta WHERE meta_key = 'etimeclockwp_surname' AND post_id=@uid;
SELECT @name:= meta_value FROM wp_postmeta WHERE meta_key = 'etimeclockwp_name' AND post_id=@uid;
SELECT @name,@surname;

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2017
Distinguished Expert 2018
Why not just do this
Select * from (
  select meta_value FROM wp_postmeta WHERE meta_key = 'etimeclockwp_surname' AND post_id=?
) A, (
  select meta_value FROM wp_postmeta WHERE meta_key = 'etimeclockwp_name' AND post_id=?
) B

Open in new window

The ? are if you are using prepared statements - otherwise add your post id to the string.

The above query joins the results from the two queries and returns a single row with the results of each query as a column.

Author

Commented:
Thank you for this I was looking how to join tables but searching for terms usch as connect , combine and nothing.
Before you posted this I actually found another way using multi query instead of single,
This let's me create queries like variables and it works and very readable.

$query .= "
SELECT @post_id_from_clock_in:= post_id FROM wp_postmeta WHERE meta_key LIKE 'etimeclockwp-in%';
SELECT @uid:= meta_value FROM wp_postmeta WHERE  meta_key ='uid';
SELECT * FROM wp_postmeta WHERE post_id =@uid;
SELECT @getSurnameByPostID:= meta_value FROM wp_postmeta WHERE post_id = @post_id;
SELECT @surname:= meta_value FROM wp_postmeta WHERE meta_key = 'etimeclockwp_surname' AND post_id=@uid;
SELECT @name:= meta_value FROM wp_postmeta WHERE meta_key = 'etimeclockwp_name' AND post_id=@uid;
SELECT * FROM wp_postmeta WHERE meta_key LIKE 'etimeclockwp-in%';
/* echos all dates  */
SELECT @date_in:=meta_value, substring_index(meta_value, '|', 1) FROM wp_postmeta WHERE meta_key LIKE 'etimeclockwp-in%';
SELECT @name,@surname,@post_id,@uid,@date_in;
";


      /* execute multi query */
      if ($mysqli->multi_query($query)) {
            do {
                  /* store first result set */
                  if ($result = $mysqli->store_result()) {
                        while ($row = $result->fetch_array()) {
                              //print_r($row)[@surname]."<br>";
                              echo $row['@surname'].' '.$row['@name'].$row['@post_id'].' '.$row['@uid'].' '.$row['@date_in'];
                        }
                  }
            }
            while ($mysqli->next_result());
      }

      /* close connection */
      $mysqli->close();
Most Valuable Expert 2017
Distinguished Expert 2018
You are welcome.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial