get Table Alias row values - MySQL Query in PHP

ajaxStardust

New member

the following SQL returns the data I need, but I don't know how to access it as I need with PHP. I can demo that the data can be used as I want as evidenced by output in a free PHP code generator by SQL Maestro, but it produces files dependent upon much more library code than I need. 

I simply want to access the row values from two separate table alias columns, Name_value and Nmbr_value, as shown in the img. 

Code:
SELECT DISTINCT Nmbr.uid as Nmbr_uid,
 Nmbr.fid as Nmbr_fid,
 Nmbr.value as Nmbr_value,
 Name.fid as Name_fid,
Name.value as Name_Value,
 u.uid as u_uid,
 u.status as u_status,
Name.uid as Name_uid
    FROM profile_values Nmbr
    INNER JOIN users u
        ON u.uid = Nmbr.uid
    INNER JOIN profile_values Name
        ON Name.uid = u.uid
    WHERE Name.fid = 1
        AND Nmbr.fid = 11
        AND Nmbr.value != ''
        AND (Name.value!='Retiree' OR Nmbr.value = '1')
    ORDER BY Name.value DESC

adminer_screenshot_20220708.png

 I've heard this type of SQL query referred to as a "SELF JOIN". In any case, I don't know how to iterate over this type of result set so I can use it e.g. to create HTML forms, etc. 
   
 Thank you!
 
To retrieve the data with php you should use some module to connect to the database and then execute you query to retrieve the results.
I can suggest to you to use the PDO.
You can find the example how to connect to the database here:
https://www.php.net/manual/en/pdo.construct.php

and how to prepare and execute the query:
https://www.php.net/manual/en/pdostatement.execute.php
 
I'm cool w/ that, and I am familiar w/ PDO.

Thank you. I was going to ask why it's necessary to use the module, but...

I appreciate your advice, and I'll do the research. Thank you very much!

:)
 
This SQL query is is for a WordPress (WP) plugin ideally, whereby WP has it's own database abstraction, the global $wpdb . I would prefer to use $wpdb for extensibility / forward compatibility, but I can't get my SQL to work with it. Not super WP savvy yet, I regret.

I couldn't get my query to work with mysqli, but perhaps $wpdb might accommodate. Testing with var_dump(), the result returns array(0) on Any ideas?

Is there a recommended way of testing the MySQL query with PHP, outside of just reloading the page in the browser?
 
Check the query and the prepare methods in $wpdb:
Code:
$wpdb->query( 
    $wpdb->prepare("DELETE FROM $wpdb->postmeta WHERE post_id = %d AND meta_key = %s", 13, 'test')
);
 
Eureka! I was testing that query on 2 different databases. Turns out, I didn’t update the credentials for one of the two, so doh! $wpdb was working all along.

Thank you for reading, and the reply nevertheless.

(there's a weekend i'll never get back...)
 
Back
Top