MySQL Query

[yggdrasil]

I'm trying to learn the new approach from v6 that uses Laravel for queries but this seems rather more messy (at least for me than the old way which is going to be deprecated.

 

For example I made this query:

 

foreach (Capsule::table('tbldomains')->where('userid', '=', {$loggedinuser.userid})->get() as $dom) {

echo $dom->domain . PHP_EOL;

 

This works and it gets the users logged in domains but the problem is that all results are into the single $dom variable as ouput.

 

I tried setting them in $row like the a standard PHP query to MySQL but it does not work. Neither arrays worked for me. The problem with this approach is that I cannot use domains separately in the code.

 

Is there an easier way to pull domains out of a users account?

[sentq]

it is very easy, if you need to get all domains in one array you need to change you code as it below, what you did is 50% correct, adding the query in foreach isn't right anyway.

 

$domains = Capsule::table('tbldomains')->where('userid', '=', $loggedinuser.userid)->get();
foreach ($domains as $domain) {
   $clientdomains[] = $domain->domain;
}
print_r($clientdomains);

[yggdrasil]

it is very easy, if you need to get all domains in one array you need to change you code as it below, what you did is 50% correct, adding the query in foreach isn't right anyway.

 

$domains = Capsule::table('tbldomains')->where('userid', '=', $loggedinuser.userid)->get();
foreach ($domains as $domain) {
   $clientdomains[] = $domain->domain;
}
print_r($clientdomains);

 

Thank you sentq, but how is your approach any different?

 

If the table pulls, lets say 50 domains, and they are all in one $clientdomains result variable, this is the same as above in my example.

 

I would need them on its own variables like:

$domain1

$domain2

$domain3

 

Or better, the actual name of the domain like this, if the domains are example.com, example.net, example3.com

 

Then convert the same results into a variable on their own like this:

 

$example.com

$example.net

$example3.com

 

The query I used, I took it from the examples on the WHMCS Docs page, and modified at bit to be able to filter it by customer account. I know its probably wrong, but I don't know how to create the proper array using this new Laravel approach which is completely new to me.

[yggdrasil]

Or I could just use WHERE AND and get 1 single domain, but that does not work either.

 

That way I could tell the query I want THIS domain ID but only if the Customer ID is this one.

 

Otherwise you could exploit the query into getting ID domains which don't belong to the customer. This is why I used the customer logged ID. Not sure if that makes sense, but I don't want to get only domains by ID but make sure the domains or just 1 domain displayed on that page actually is from that user account.

 

If there is a better way to do this in terms of security, I'm open to suggestions.

[brian!]

just so i'm sure on this, are you wanting this as a hook to pass the created array/variables back to the template as Smarty?

[yggdrasil]

Well, not really. I usually just use the PHP variables directly. I never tried to turn them back as smarty since I could just use them directly in the page enclosed with php tags (yes I enabled PHP in smarty because that is what I know how to use and otherwise how would I query things and make a page dynamic).

 

This new approach is way too complicated to me. Simple things like this:

$result = mysql_query("SELECT $query1 FROM $table0 WHERE `userid` = '{$loggedinuser.userid}'");

 

I cannot replicate them in the Laravel approach.

 

Having to learn a new syntax is a real pain, in particular when I had my queries working fine in v5.

 

I had several queries, getting 1 result, getting multiple ones in some array, using WHERE AND OR

 

They still work in v6, but it seems they plan to deprecate them in the future so now I have to learn a new messy syntax...I honestly prefer if WHMCS does not do this and still let us access the database directly with the language I want, and people know, this means PHP or Smarty, in the future they may drop Laravel and use something else and the game is on again.

 

The above example is clean and you know exactly what it does by just looking at it. Do I need more results? No problem I just add more queries:

 

mysql_query("SELECT $query1, $query2, $query3 FROM $table0

 

Do I need to convert them to variables. Again:

while($row = mysql_fetch_array($result))

{

$myvar1= $row[$query1];

$myvar2= $row[$query2];

$myvar3= $row[$query3];

}

 

I have read the Laravel docs and it's a huge mess. Very simple things are way more complicated to do, they mix things and the code is not even clear when you look at it. For example they say the use AND automatically when you use:

Table::where('Column', Value)->where('NewColumn', Value)->get();

 

Why?

 

That is not clear and you should not be doing invisible things that nobody knows what is doing.

 

So now I'm supposed to guess what the code does. Not nice.

[brian!]

Well, not really. I usually just use the PHP variables directly. I never tried to turn them back as smarty since I could just use them directly in the page enclosed with php tags (yes I enabled PHP in smarty because that is what I know how to use and otherwise how would I query things and make a page dynamic).

I bet that {php} tags will be deprecated long before the mysql stuff is - so that's a habit you'll need to get out of using too.

 

They still work in v6, but it seems they plan to deprecate them in the future so now I have to learn a new messy syntax...I honestly prefer if WHMCS does not do this and still let us access the database directly with the language I want, and people know, this means PHP or Smarty, in the future they may drop Laravel and use something else and the game is on again.

to be fair to WHMCS, I suspect they're only doing this because the mysql stuff is being removed from PHP - so that had to do something (if not with v6, then certainly at some future point).

 

I have read the Laravel docs and it's a huge mess. Very simple things are way more complicated to do, they mix things and the code is not even clear when you look at it.

So now I'm supposed to guess what the code does. Not nice.

lots of new v6 features are badly documented - both on the WHMCS docs site and their source sites... it's a real pain.

 

I answered a thread yesterday and wanted to use Laravel in the answer - couldn't get it working (it needed multiple tables and queries) and I couldn't be bothered messing around with joins - if it were a paid project, i'd have spent time figuring it out, but it was easier to just post the mySQL method instead.

 

Thank you sentq, but how is your approach any different?

it's certainly far better from a programming point of view.

 

If the table pulls, lets say 50 domains, and they are all in one $clientdomains result variable, this is the same as above in my example.

depending on what you want to do with the information, it might be easier to have them all in one array.

 

as a example, let's say we make a hook from sentq's modified code to do this... for simplicity, i'm pulling the ID from the session, but you could do it your way if you pulled the smarty variable into the hook...

 

<?php

use Illuminate\Database\Capsule\Manager as Capsule;

function domains_list_hook($vars) {

   $userid = $_SESSION['uid'];
   $domains = Capsule::table('tbldomains')
                   ->where('userid', $userid)
                   ->get();

   foreach ($domains as $domain) {
       $clientdomains[] = $domain->domain;
   }

   $return = array("clientsdomains" => $clientdomains);
   return $return;
}

add_hook('ClientAreaPageHome', 1, 'domains_list_hook');
?>

that will create a single Smarty array, only available to the client home page, containing a list of domains for that client... but you don't want that.

 

I would need them on its own variables like:

$domain1

$domain2

$domain3

then you could change the return line in the above code to...

 

     $return = array("domains1" => $clientdomains[0], "domain2" =>  $clientdomains[1], "domain3" => $clientdomains[2]);

 

Or better, the actual name of the domain like this, if the domains are example.com, example.net, example3.com

Then convert the same results into a variable on their own like this:

 

$example.com

$example.net

$example3.com

then you could change the return line in the above code to...

 

     $return = array($clientdomains[0] => $clientdomains[0], $clientdomains[1] => $clientdomains[1], $clientdomains[2] => $clientdomains[2]);

obviously, you would ideally use a loop to generate these instead of the above method - but that's just basic php, so the above is just to show the output method required... perhaps you can put it back into {php} tags if that's what you prefer.

[yggdrasil]

I agree. Actually the Docs in WHMCS that points to the Laravel SQL query page is also broken.

 

WHMCS should at least put some basic examples up in the docs. For an idiot like me, they do help, because I use them as a basis for very simple stuff. I'm not a coder so I don't do coding except when I'm forced to do it because there is no other way. I don't like to learn different ways of doing things for every software, I understand Laravel is cleaning the code in order to avoid SQL injections but that is a false sense of security, in particular when you don't know what you are doing like me. I would prefer to use what I know and on what both Smarty, WHMCS and even Laravel is build which is PHP and MySQL.

 

Every coding language respects the SQL syntax's regardless of the database type, and its more or less the same, regardless if you are doing queries directly in a console or from a language. It seems Laravel does not and since its not that popular like Smarty, you cannot Google things and just find the results.

 

I do appreciate your examples, but it should be WHMCS posting them, they have a blog for this.