get Table Alias row values - MySQL Query in PHP

Codes here !

Moderators: gesf, Michalio

Post Reply
User avatar
ajaxStardust
New php-forum User
New php-forum User
Posts: 5
Joined: Fri Jul 08, 2022 3:10 am


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 all

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
Image
 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!
User avatar
Michalio
Moderator
Moderator
Posts: 209
Joined: Sun Jul 18, 2021 1:33 pm
Location: Poland

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
Free coding lessons: https://php-forum.com/phpforum/viewtopic.php?t=29852
User avatar
ajaxStardust
New php-forum User
New php-forum User
Posts: 5
Joined: Fri Jul 08, 2022 3:10 am

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!

:)
User avatar
ajaxStardust
New php-forum User
New php-forum User
Posts: 5
Joined: Fri Jul 08, 2022 3:10 am

Thank you! PDO is definitely what I needed here. Much appreciated. :)
User avatar
ajaxStardust
New php-forum User
New php-forum User
Posts: 5
Joined: Fri Jul 08, 2022 3:10 am

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?
User avatar
Michalio
Moderator
Moderator
Posts: 209
Joined: Sun Jul 18, 2021 1:33 pm
Location: Poland

Check the query and the prepare methods in $wpdb:

Code: Select all

$wpdb->query( 
    $wpdb->prepare("DELETE FROM $wpdb->postmeta WHERE post_id = %d AND meta_key = %s", 13, 'test')
);
Free coding lessons: https://php-forum.com/phpforum/viewtopic.php?t=29852
User avatar
ajaxStardust
New php-forum User
New php-forum User
Posts: 5
Joined: Fri Jul 08, 2022 3:10 am

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...)
Post Reply