sqlValue()

Discussions related to customizing hooks. Hooks are documented at http://bigprof.com/appgini/help/advanced-topics/hooks/
Post Reply
dannybridi
Veteran Member
Posts: 47
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: 711
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 />

pböttcher
AppGini Super Hero
AppGini Super Hero
Posts: 1005
Joined: 2018-04-01 10:12

Re: sqlValue()

Post by pböttcher » 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).

dannybridi
Veteran Member
Posts: 47
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 :-)

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 568
Joined: 2019-05-21 22:42
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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

User avatar
jsetzer
AppGini Super Hero
AppGini Super Hero
Posts: 711
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 />

onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 568
Joined: 2019-05-21 22:42
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
Some postings I was involved, you might find useful:
Backup your database (viewtopic.php?f=4&t=3341); Improve security (viewtopic.php?f=4&t=3168); Field Permissions (viewtopic.php?f=4&t=3308); Custom (error) message (viewtopic.php?f=7&t=1740&p=10871#p10906); Audit Log (viewtopic.php?f=4&t=1369&p=10407); Two Factor Authentication (viewtopic.php?f=7&t=3306&p=11478); Add 2nd SAVE CHANGES button (viewtopic.php?f=2&t=3242&p=11104); Place a search on details view (viewtopic.php?f=2&t=3479&p=12484#p12484); Column-Value-Based-Permissions (viewtopic.php?f=4&t=3498)

Post Reply