sqlValue()

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
dannybridi
Veteran Member
Posts: 54
Joined: 2016-03-21 19:33

sqlValue()

Post by dannybridi » 2020-02-08 20:42

Hello,

Could someone explain to me the sqlValue() function?

I'm looking for a way to send an SQL statement (something like "select * from table1 where col1=123") from a hook and store the response in a variable. I thought the sqlValue function would to that, but it doesn't look like it.

Is it a PHP function?

Thanks

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: sqlValue()

Post by jsetzer » 2020-02-08 21:03

Hi!

You have answered your question yourself:

Code: Select all

$sql = "select my_result_column from table1 where col1=123";
$result = sqlValue($sql);
There is nothing more than this.

Sometimes it makes sense to check your SQL command using your sql workbench like phpMyAdmin or, which I prefer, Adminer.

Always care for letter casing of table- and column-names and for correct qoutes.

If you need more than one value, for example all values of a row as an array, please consult the docs for the sql($sqlcommand, $eo) function. This will return a so called resource handle (similar to a database 'cursor') which can be iterated to fetch row by row.

Code: Select all

$eo = null;
$rows = [];
$sql = "SELECT * FROM table1";
$res = sql($sql, $eo);
if (!$eo) {
  while ($row = db_fetch_assoc($res)) $rows[] = $row;
}
// now you can use the $rows array
Hope this helps,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: sqlValue()

Post by pbottcher » 2020-02-08 21:11

Maybe to add to this.

The SQLVALUE function returns indeed one value that you look for.

The statement you describe in your question would return all fields for the selected row in your database, which would probably not be only one field and hence the the SQLVALUE function would not be the choise. In this case, even if you want only one row you need to use the SQL function.

Internally the SQLVALUE function uses also the SQL function, but gives you only one value as result (if the sql statement provides that).

So sqlvalue("SELECT * from table where ID = 1") would fail, but
sqlvalue("SELECT id from table where ID=1") would retrun 1 (not very clever but I hope it explains it).
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

dannybridi
Veteran Member
Posts: 54
Joined: 2016-03-21 19:33

Re: sqlValue()

Post by dannybridi » 2020-02-08 21:22

Thank you both. Your answers clarified my confused mind :-)

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: sqlValue()

Post by onoehring » 2020-02-12 19:01

Hi Jan,

just a quick question:
Why do you use

Code: Select all

if (!$eo) {
before accessing the array?
Olaf

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 1807
Joined: 2018-07-06 06:03
Location: Kiel, Germany
Contact:

Re: sqlValue()

Post by jsetzer » 2020-02-12 19:18

Hi Olaf!

Because of error handling.

For testing purposes and for better understanding you can try to break your SQL command and then dump and check the return value of sql() and also the error output $eo.

You will see the difference between a valid resource handle from a valid SQL query AND the return value and $eo if you did a mistake in SQL or any input parameter broke the command.

Are there any bugs or crashes with my code?

Best,
Jan
Kind regards,
<js />

My AppGini Blog:
https://appgini.bizzworxx.de/blog

You can help us helping you:
Please always put code fragments inside [code]...[/code] blocks for better readability

AppGini 24.10 Revision 1579 + all AppGini Helper tools

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: sqlValue()

Post by onoehring » 2020-02-13 14:35

Hi Jan,

thanks. So $eo is for error output. I missed that ... somewhere :-)

Olaf

Post Reply