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!

ajaxStardust I don't know how to iterate over this type of result set

Unless I'm missing something, you'd do it the same way you'd iterate over any query result: most likely a while() loop on a "fetch" function/method for whatever database extension you're using?

    mysqli didn't work. I used PDO. It was just to test whether I was able to get at the data (basically).

    function getSMSUsers($conn)
{
$pdoConn = $conn->prepare("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");

$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>";
    
    print "<br>";
    
}
}

    Thank you!

    ajaxStardust I used PDO

    That's all I use any more, and find it much cleaner to use than MySQLi, so recommend sticking with it going forward if practical for you. YMMV

      If you're loading the entire result set into an array before working with it (which is often reasonable), the fetch loop can be replaced by a single fetchAll() call.

      (And, just to be nitpicky, $pdoConn is a bit poorly-named, since it isn't a PDO connection, but a PDO statement. I'd suggest a better name, but I don't know what the results mean so I can't make a meaningful suggestion.)

      Weedpacket a single fetchAll() call

      As long as you have good reason to think you'll never get a huge query result set (for some undefined value of "huge"), which would result in a correspondingly huge array in memory -- not as bad now in recent versions of PHP than older ones where arrays could consume even more memory.

        NogDog Yes; in situations where the result set is likely to be huge you wouldn't be loading the entire result set into an array anyway. (That said, how huge is "huge"?)

          NogDog
          Weedpacket
          Thanks for the replies. I'd like to explore this more. Why is it not advised to use an array for a "huge" result set, and what would be the alternative?

          This particular DB is only returning about 150 rows, so quite manageable at this stage, and likely not to grow exponentially beyond that. However, I suppose the goal would always be to code for maximum extensibility?

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

              ajaxStardust Thanks for the replies. I'd like to explore this more. Why is it not advised to use an array for a "huge" result set, and what would be the alternative?

              A huge dataset (where "huge" is something that makes a noticeable dent in the server's available memory) would cause problems if there are multiple instances of the PHP application running, serving multiple users. Even if none of them individually hit the (default) cap of 128MB, all together they might put a strain on the server. Even if the server doesn't have to start swapping, its file cache would have to be flushed and anything in there would need to be read from disk again, slowing it down, for example. Then there is the delay involved in simply moving all that data into the application (which is why filtering database records should be done in the database).

              What should be done if you have a huge dataset depends on the dataset and how you're using it. Iterating through database records doing something with each one would be handled by ... well, iterating through the records and doing whatever is needed with each one. So that you don't have to have all the records in hand to start with.

              ajaxStardust Thanks for pointing that out. I've seen $dbh used in most examples, so I guess that's the standard way to go?

              Examples are written without reference to considerations that apply to real-world code. Real-world code would use a name for the variable that describes what's actually in it. Like I said, I have no idea what these records you're selecting represent so I can't suggest what the variable should be called. Something about "retiree"s and some magic numbers, but what does it mean? For example, a script that's running on my machine right now has two PDO statements (connected to two different databases) in variables named $image_paths and $thumbnail_paths.

                    Write a Reply...