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

Code: Select all

if (!$eo) {
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