Page 1 of 1
sqlValue()
Posted: 2020-02-08 20:42
by dannybridi
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
Re: sqlValue()
Posted: 2020-02-08 21:03
by jsetzer
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
Re: sqlValue()
Posted: 2020-02-08 21:11
by pbottcher
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).
Re: sqlValue()
Posted: 2020-02-08 21:22
by dannybridi
Thank you both. Your answers clarified my confused mind
Re: sqlValue()
Posted: 2020-02-12 19:01
by onoehring
Hi Jan,
just a quick question:
Why do you use
before accessing the array?
Olaf
Re: sqlValue()
Posted: 2020-02-12 19:18
by jsetzer
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
Re: sqlValue()
Posted: 2020-02-13 14:35
by onoehring
Hi Jan,
thanks. So $eo is for error output. I missed that ... somewhere
Olaf