get Table Alias row values - MySQL Query in PHP

[ajaxStardust]

the following SQL returns the data I need, but I don't know how to access it as I need with PHP. Using this free little app I can demo that the data can be used as I want, but it produces files dependent upon much more library code than I need (of course). 

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

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

    

   
   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!

Edited July 8, 2022 by ajaxStardust
formatting

[Barand]

What have to tried so far?

How are you connecting to the database?

Have look at phpdelusions site

If you aren't already using PDO the I recommend you do.

[ajaxStardust]

I was just trying mysqli and a simple foreach loop, and trying E.g. $data['Nmbr_Value'] , $data['Name_Value']. 

Edited July 8, 2022 by ajaxStardust

[Barand]

How are you getting "$data" array?

[ginerjm]

As Barand has asked why not show us the code that is used to run that query and from that we can probably tell you how to access it.  And certainly using the PDO interface is a much better choice over mysqli.

[mac_gyver]

2 hours ago, ajaxStardust said:

Nmbr_value

 

6 minutes ago, ajaxStardust said:

$data['Nmbr_Value']

Nmbr_value and Nmbr_Value are not the same. php is case-sensitive concerning variable/property/associative index names. consistency counts in programming. always using all lowercase identifier names won't have you trying to remember what they should be.

[ajaxStardust]

@ginerjm @Barand
It's really just at a very beginning stage of testing:
 

function getSMSUsers($conn)
{
    $res = $conn->query("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
	");
    
    print "<div><ol>";
    foreach($res as $row_key => $row_val){
        foreach($row_val as $thisRow){
        print_r($thisRow);
        // print "<li>".$row_val['Name_Value']."</li>";
      	// print "<li>".$row_val['Nmbr_Value']."</li>";
        }
    }
    print "</ol></div>"; 
 
}

EDIT:
the print_r($thisRow) does show the data, but it's not practical of course. 

Edited July 8, 2022 by ajaxStardust

[Barand]

Here's some ways to skin that cat ...

A

foreach ($res as $row) {
    echo "<div><ol>";
    echo "<li>{$row['Nmbr_fid']}</li>";
    echo "<li>{$row['Nmbr_value']}</li>";
    echo "<li>{$row['Name_fid']}</li>";
    echo "<li>{$row['Name_Value']}</li>";
    . . .
    echo "</ol></div>";
    
}

B

foreach ($res as $row) {
    echo "<div><ol>";
    foreach ($row as $col) {
        echo "<li>$col</li>";
    }
    echo "</ol></div>";
}

C

foreach ($res as $row) {
    echo "<div><ol><li>" . join('</li><li>', $row) . '</li></ol></div>';
}

 

[ajaxStardust]

Thank you, all! 

PDO is definitely what I needed here. 

@Barand This is what I came up with. I've got my values. I really appreciate the insightful, useful replies here!
 

  ...AND (Name.value!='Retiree' OR Nmbr.value = '1')
 ORDER BY Name.value DESC");

$name_val = $pdoConn->bindValue(":Name_value", 32);
$nmbr_val = $pdoConn->bindValue(":Nmbr_value", 10);

$tryPrint = array();
$pdoConn->execute();

while($row = $pdoConn->fetch(PDO::FETCH_ASSOC)){
$tryPrint[] = $row;
}

foreach($tryPrint as $try_row => $try_val){   
print "Row $try_row: <br>";
print "Name: ".$try_val['Name_value']."<br>";
print "Nmbr: ".$try_val['Nmbr_value']."<br>";

}

 

[ginerjm]

Perhaps a simpler less involved output method:

$rownum = 0;
while($row = $pdoConn->fetch(PDO::FETCH_ASSOC))
{
	echo "
	Row $rownum:  
	Name: {$row['Name_value']} 
	Nmbr: {$row['Nmbr_value']}<br>
	";
	$rownum++;
}

Don't know why you put each value on its own row so I dropped it.  You could change the nbsp value to a <br> again if that's what you really wanted.

Basically there is no need to process the query results (rows) into another array only to do the output.

[ajaxStardust]

@ginerjm This was just to test the data access / result set. nothing more. Now that I have that, I'm good to go. 

Thank you all! 

[ginerjm]

Well at least you won't be wasting the typing and resources to build a separate array when you finally do it.

[mac_gyver]

PDO has a fetchAll() method that fetches all the rows of data from a query at once, and you should set the default fetch mode to assoc when you make the database connection so that you don't need to specify it in each fetch statement.

also, based on the whole query posted elsewhere, this is not a prepared query. there's no actual place-holders in it. the only reason it isn't producing an error is probably because you are using an emulated prepared query. when you make the connection you should set emulated prepared queries to false, i.e. you want to run real prepared queries.

your function getSMSUsers, should only get and return the data. it should not also be responsible for producing any of the presentation output, nor should any function actually echo/print any output they produce, only return it so that the calling code can us it in whatever context it is being used in.

[ajaxStardust]

This SQL query is is for a WordPress (WP) plugin ideally, whereby WP has it's own database abstraction, the $wpdb class . 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 testing with a simple query (as I did with PDO per suggestions, here), but perhaps $wpdb might accommodate. Testing with var_dump(), the result returns array(0) and I've tried various permutations of the $wpdb methods without success. 

Is there a recommended way of testing the MySQL query with PHP, outside of just reloading the page in the browser? 

Edited July 11, 2022 by ajaxStardust
link for reference

[mac_gyver]

if you have a wp based query/code that you need help with, you will need to post it.

if your goal is to use the wpdb class or the PDO extension, there's no point in wasting time with the mysqli extension. note: you can use the PDO extension within WP, by getting the WP configuration connection credentials and making your own PDO connection.

1 hour ago, ajaxStardust said:

I've tried various permutations of the $wpdb methods without success

using the try it, throw it away if it doesn't work, try something else method doesn't result in any actual learning and takes a long time, sometimes never, to accomplish any programming. if something doesn't work, you need to find out why, both so that you will learn what was right or wrong with your understanding of the attempted solution and so that you can keep the parts that work and just do the work needed to fix the part(s) that don't work.

2 hours ago, ajaxStardust said:

Is there a recommended way of testing the MySQL query with PHP, outside of just reloading the page in the browser?

if this is asking about developing and testing the actual sql query statement, do this directly against the database using a tool like phpmyadmin, which i'm guessing the image at the top of this thread is the result of. if this is asking something else, please clarify.

[ajaxStardust]

Eureka! I was testing that query on 2 different databases. Turns out, I didn’t update the credentials for one of the two after some table tweaking, so... doh! That was just a stupid oversight on my part.

$wpdb was working all along.

Thank you for reading, and the reply nevertheless.