Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.lang.php > #14480 > unrolled thread

Pulling ad-hoc data from a database into a PHP web page

Back to article view | Back to comp.lang.php

Contents

  Pulling ad-hoc data from a database into a PHP web page "James Harris" <james.harris.1@gmail.com> - 2014-11-06 00:23 +0000
    Re: Pulling ad-hoc data from a database into a PHP web page Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-11-06 04:54 +0100
      Re: Pulling ad-hoc data from a database into a PHP web page Jerry Stuckle <jstucklex@attglobal.net> - 2014-11-06 09:19 -0500
        Re: Pulling ad-hoc data from a database into a PHP web page "James Harris" <james.harris.1@gmail.com> - 2014-11-06 15:39 +0000
          Re: Pulling ad-hoc data from a database into a PHP web page Jerry Stuckle <jstucklex@attglobal.net> - 2014-11-06 11:32 -0500
            Re: Pulling ad-hoc data from a database into a PHP web page "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2014-11-26 10:26 +0100
              Re: Pulling ad-hoc data from a database into a PHP web page Tim Streater <timstreater@greenbee.net> - 2014-11-26 09:38 +0000
                Re: Pulling ad-hoc data from a database into a PHP web page "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2014-11-26 11:56 +0100
              Re: Pulling ad-hoc data from a database into a PHP web page Jerry Stuckle <jstucklex@attglobal.net> - 2014-11-26 07:51 -0500
                Re: Pulling ad-hoc data from a database into a PHP web page "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2014-11-26 14:24 +0100
                  Re: Pulling ad-hoc data from a database into a PHP web page Jerry Stuckle <jstucklex@attglobal.net> - 2014-11-26 08:36 -0500
                    Re: Pulling ad-hoc data from a database into a PHP web page Matthew Carter <m@ahungry.com> - 2014-11-26 10:21 -0500
                      Re: Pulling ad-hoc data from a database into a PHP web page "M. Strobel" <sorry_no_mail_here@nowhere.dee> - 2014-11-26 20:57 +0100
                      Re: Pulling ad-hoc data from a database into a PHP web page Jerry Stuckle <jstucklex@attglobal.net> - 2014-11-26 16:21 -0500
          Re: Pulling ad-hoc data from a database into a PHP web page "Christoph M. Becker" <cmbecker69@arcor.de> - 2014-11-06 18:27 +0100
      Re: Pulling ad-hoc data from a database into a PHP web page "James Harris" <james.harris.1@gmail.com> - 2014-11-06 15:58 +0000
        Re: Pulling ad-hoc data from a database into a PHP web page Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-11-06 21:34 +0100
    Re: Pulling ad-hoc data from a database into a PHP web page Jerry Stuckle <jstucklex@attglobal.net> - 2014-11-05 23:00 -0500
      Re: Pulling ad-hoc data from a database into a PHP web page "James Harris" <james.harris.1@gmail.com> - 2014-11-06 15:29 +0000
        Re: Pulling ad-hoc data from a database into a PHP web page Matthew Carter <m@ahungry.com> - 2014-11-06 10:44 -0500
          Re: Pulling ad-hoc data from a database into a PHP web page "James Harris" <james.harris.1@gmail.com> - 2014-11-06 16:15 +0000
            Re: Pulling ad-hoc data from a database into a PHP web page Matthew Carter <m@ahungry.com> - 2014-11-06 11:25 -0500
              Re: Pulling ad-hoc data from a database into a PHP web page Jerry Stuckle <jstucklex@attglobal.net> - 2014-11-06 12:42 -0500
              Re: Pulling ad-hoc data from a database into a PHP web page Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-11-08 13:50 +0100
                Re: Pulling ad-hoc data from a database into a PHP web page Matthew Carter <m@ahungry.com> - 2014-11-08 21:19 -0500
                  Re: Pulling ad-hoc data from a database into a PHP web page Jerry Stuckle <jstucklex@attglobal.net> - 2014-11-08 22:08 -0500
                  Re: Pulling ad-hoc data from a database into a PHP web page Thomas 'PointedEars' Lahn <PointedEars@web.de> - 2014-11-09 08:09 +0100
            Re: Pulling ad-hoc data from a database into a PHP web page "Christoph M. Becker" <cmbecker69@arcor.de> - 2014-11-06 18:24 +0100
              Re: Pulling ad-hoc data from a database into a PHP web page Matthew Carter <m@ahungry.com> - 2014-11-06 12:37 -0500
              Re: Pulling ad-hoc data from a database into a PHP web page "James Harris" <james.harris.1@gmail.com> - 2014-11-06 17:51 +0000
                Re: Pulling ad-hoc data from a database into a PHP web page "Christoph M. Becker" <cmbecker69@arcor.de> - 2014-11-06 20:56 +0100
        Re: Pulling ad-hoc data from a database into a PHP web page Jerry Stuckle <jstucklex@attglobal.net> - 2014-11-06 12:40 -0500
        Re: Pulling ad-hoc data from a database into a PHP web page richard <noreply@example.com> - 2014-11-10 11:17 -0500
          Re: Pulling ad-hoc data from a database into a PHP web page Denis McMahon <denismfmcmahon@gmail.com> - 2014-11-10 21:27 +0000

Page 2 of 2 — ← Prev page 1 [2]

#14497

"Matthew Carter" <m@ahungry.com> wrote in message 
news:87oaskxriy.fsf@ahungry.com...
> "James Harris" <james.harris.1@gmail.com> writes:

...

>> function scalar_get($key) {
>>   global $sv, $db, $un, $pw;
>>   echo "In function scalar_get($key)<br/>";
>>   echo "Make db connection to $db<br/>";
>>
>>   $conn = mysqli_connect($sv, $un, $pw);
>>   if (!$conn) {
>>     die("Database connection failed: " . mysqli_connect_error());
>>   }
>>   echo "Database connected successfully<br/>";
>>
>>   $sql = "select value from $db.scalars where id = \"$key\";";
>>   $result = mysqli_query($conn, $sql);
>>   if ($result === FALSE) {
>>     echo "Query ($sql) failed: " . mysqli_error($conn) . "<br/>";
>>   } elseif (mysqli_num_rows($result) !== 1) {
>>     echo "Expected one row, got " . mysqli_num_rows($result) . "<br/>";
>>   } else {
>>     echo "Got one row<br/>";
>>     $row = mysqli_fetch_row($result);
>>     echo "Result is ($row[0])<br/>";
>>   }
>> }
>>
>> The idea is that a function like the above (but less verbose!) would be
>> called an arbitrary number of times in the main body of a page by means 
>> of
>> calls such as the following. At least for scalars I may get it to return 
>> the
>> string instead of printing it.
>>
>>   scalar_get("forename");
>>   echo "<br/>";
>>   scalar_get("surname");
>>   echo "<br/>";

...

> I would either:
>  - Wrap the function in a class and store the DB connection as a class
>  variable
>  - Pass the connection as an additional parameter to the function
>  - If you insist on including a 'global' line in there, use the
>  connection as the global (instead of the name/pass etc.)

Thanks. The latter sounds simple and is apparently something I already know 
how to do - because I've just tried it with string variables. It would also 
be sufficient for my needs just now.

A query though: where is it best to have the PHP code which makes the 
database connection? Should it go
* before the start of the page (such as before any initial <html> tag)
* in the <head>...</head> section
* at the top of the <body> section
or does it not matter as long as the code to make the connection comes 
before the functions which use it?

> Opening a database each time you want to do a single query is more
> inefficient than opening and keeping open.
>
> Is your database going to be two column like this?
>
>    --------------
>    | id | value |
>    --------------

The table containing the scalars will be exactly like that. (As an aside I 
think of this as a table of key-value pairs and was going to use "key" as a 
column name but mysql objected so I had to change it to "id"; I guess that 
the former is a reserved word as it is used in other SQL contexts even 
though it is not valid in the select context I was using.)

> If so, you could probably make 'id' your unique key and omit your elseif
> clause in the row checking snippet (it seems like having duplicate ids
> would break the manner in which you want to query this information).

Understood. Yes, it is a unique key.

James

[toc] | [prev] | [next] | [standalone]

#14498

"James Harris" <james.harris.1@gmail.com> writes:

> "Matthew Carter" <m@ahungry.com> wrote in message 
> news:87oaskxriy.fsf@ahungry.com...
>> "James Harris" <james.harris.1@gmail.com> writes:
>
> ...
>
>>> function scalar_get($key) {
>>>   global $sv, $db, $un, $pw;
>>>   echo "In function scalar_get($key)<br/>";
>>>   echo "Make db connection to $db<br/>";
>>>
>>>   $conn = mysqli_connect($sv, $un, $pw);
>>>   if (!$conn) {
>>>     die("Database connection failed: " . mysqli_connect_error());
>>>   }
>>>   echo "Database connected successfully<br/>";
>>>
>>>   $sql = "select value from $db.scalars where id = \"$key\";";
>>>   $result = mysqli_query($conn, $sql);
>>>   if ($result === FALSE) {
>>>     echo "Query ($sql) failed: " . mysqli_error($conn) . "<br/>";
>>>   } elseif (mysqli_num_rows($result) !== 1) {
>>>     echo "Expected one row, got " . mysqli_num_rows($result) . "<br/>";
>>>   } else {
>>>     echo "Got one row<br/>";
>>>     $row = mysqli_fetch_row($result);
>>>     echo "Result is ($row[0])<br/>";
>>>   }
>>> }
>>>
>>> The idea is that a function like the above (but less verbose!) would be
>>> called an arbitrary number of times in the main body of a page by means 
>>> of
>>> calls such as the following. At least for scalars I may get it to return 
>>> the
>>> string instead of printing it.
>>>
>>>   scalar_get("forename");
>>>   echo "<br/>";
>>>   scalar_get("surname");
>>>   echo "<br/>";
>
> ...
>
>> I would either:
>>  - Wrap the function in a class and store the DB connection as a class
>>  variable
>>  - Pass the connection as an additional parameter to the function
>>  - If you insist on including a 'global' line in there, use the
>>  connection as the global (instead of the name/pass etc.)
>
> Thanks. The latter sounds simple and is apparently something I already know 
> how to do - because I've just tried it with string variables. It would also 
> be sufficient for my needs just now.
>
> A query though: where is it best to have the PHP code which makes the 
> database connection? Should it go
> * before the start of the page (such as before any initial <html> tag)
> * in the <head>...</head> section
> * at the top of the <body> section
> or does it not matter as long as the code to make the connection comes 
> before the functions which use it?
>
>> Opening a database each time you want to do a single query is more
>> inefficient than opening and keeping open.
>>
>> Is your database going to be two column like this?
>>
>>    --------------
>>    | id | value |
>>    --------------
>
> The table containing the scalars will be exactly like that. (As an aside I 
> think of this as a table of key-value pairs and was going to use "key" as a 
> column name but mysql objected so I had to change it to "id"; I guess that 
> the former is a reserved word as it is used in other SQL contexts even 
> though it is not valid in the select context I was using.)
>
>> If so, you could probably make 'id' your unique key and omit your elseif
>> clause in the row checking snippet (it seems like having duplicate ids
>> would break the manner in which you want to query this information).
>
> Understood. Yes, it is a unique key.
>
> James
>
>

I would establish your database connection first, define any functions
and then proceed to HTML output (and the function calls within there
when necessary) if not using a template system like Smarty etc.

You can get around reserved words in mysql via backticks, such as
referring to key as `key`, but to avoid confusion, sticking to
non-reserved words is normally easier.

-- 
Matthew Carter (m@ahungry.com)
http://ahungry.com

[toc] | [prev] | [next] | [standalone]

#14504

On 11/6/2014 11:25 AM, Matthew Carter wrote:
> 
> You can get around reserved words in mysql via backticks, such as
> referring to key as `key`, but to avoid confusion, sticking to
> non-reserved words is normally easier.
> 

AMEN!

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#14510

Matthew Carter wrote:

> You can get around reserved words in mysql via backticks, such as
> referring to key as `key`, but to avoid confusion, sticking to
> non-reserved words is normally easier.

Your logic is flawed.  You cannot know which words *will* be reserved.

Please trim your quotes.
 
-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#14511

Thomas 'PointedEars' Lahn <PointedEars@web.de> writes:

> Matthew Carter wrote:
>
>> You can get around reserved words in mysql via backticks, such as
>> referring to key as `key`, but to avoid confusion, sticking to
>> non-reserved words is normally easier.
>
> Your logic is flawed.  You cannot know which words *will* be reserved.
>
> Please trim your quotes.

Why can't you know which are reserved words?  There are not that many
and there is a table view of them here:

https://dev.mysql.com/doc/refman/5.1/en/reserved-words.html

In any good editing environment (like emacs) it is trivial to add them
to the syntax highlighter so when typed they are identified as reserved
via a special syntax color (if checking the docs is too much effort).

Anyone who uses MySQL on a day to day basis would (I assume) have the
majority of these memorized (I don't see any I had not known previously
and its been many years since I accidentally named a column after a
reserved word).

Thanks for the suggestion on trimming quotes - new to Usenet and the
etiquette.

-- 
Matthew Carter (m@ahungry.com)
http://ahungry.com

[toc] | [prev] | [next] | [standalone]

#14512

On 11/8/2014 9:19 PM, Matthew Carter wrote:
> Thomas 'PointedEars' Lahn <PointedEars@web.de> writes:
> 
>> Matthew Carter wrote:
>>
>>> You can get around reserved words in mysql via backticks, such as
>>> referring to key as `key`, but to avoid confusion, sticking to
>>> non-reserved words is normally easier.
>>
>> Your logic is flawed.  You cannot know which words *will* be reserved.
>>
>> Please trim your quotes.
> 
> Why can't you know which are reserved words?  There are not that many
> and there is a table view of them here:
> 
> https://dev.mysql.com/doc/refman/5.1/en/reserved-words.html
> 
> In any good editing environment (like emacs) it is trivial to add them
> to the syntax highlighter so when typed they are identified as reserved
> via a special syntax color (if checking the docs is too much effort).
> 
> Anyone who uses MySQL on a day to day basis would (I assume) have the
> majority of these memorized (I don't see any I had not known previously
> and its been many years since I accidentally named a column after a
> reserved word).
> 
> Thanks for the suggestion on trimming quotes - new to Usenet and the
> etiquette.
> 

Matthew,

Just ignore Pointed Head.  He's well-known as a pedantic troll in
several newsgroups.


-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#14513

Matthew Carter wrote:

> Thomas 'PointedEars' Lahn <PointedEars@web.de> writes:
>> Matthew Carter wrote:
>>> You can get around reserved words in mysql via backticks, such as
>>> referring to key as `key`, but to avoid confusion, sticking to
>>> non-reserved words is normally easier.
>>
>> Your logic is flawed.  You cannot know which words *will* be reserved.
>> […]
> 
> Why can't you know which are reserved words?

The emphasized key word is “will”.

-- 
PointedEars
Zend Certified PHP Engineer
Twitter: @PointedEars2
Please do not Cc: me. / Bitte keine Kopien per E-Mail.

[toc] | [prev] | [next] | [standalone]

#14500

Am 06.11.2014 um 17:15 schrieb James Harris:
> "Matthew Carter" <m@ahungry.com> wrote in message 
> news:87oaskxriy.fsf@ahungry.com...
>> "James Harris" <james.harris.1@gmail.com> writes:
>>
>>>   scalar_get("forename");
>>>   echo "<br/>";
>>>   scalar_get("surname");
>>>   echo "<br/>";
>>
>> Is your database going to be two column like this?
>>
>>    --------------
>>    | id | value |
>>    --------------
> 
> The table containing the scalars will be exactly like that.

I'm rather confused.  Assuming that "forename" and "surname" refer to a
person, this way you can only store a single person's data in the table.
 Is that really your intention?

-- 
Christoph M. Becker

[toc] | [prev] | [next] | [standalone]

#14502

"Christoph M. Becker" <cmbecker69@arcor.de> writes:

> Am 06.11.2014 um 17:15 schrieb James Harris:
>> "Matthew Carter" <m@ahungry.com> wrote in message 
>> news:87oaskxriy.fsf@ahungry.com...
>>> "James Harris" <james.harris.1@gmail.com> writes:
>>>
>>>>   scalar_get("forename");
>>>>   echo "<br/>";
>>>>   scalar_get("surname");
>>>>   echo "<br/>";
>>>
>>> Is your database going to be two column like this?
>>>
>>>    --------------
>>>    | id | value |
>>>    --------------
>> 
>> The table containing the scalars will be exactly like that.
>
> I'm rather confused.  Assuming that "forename" and "surname" refer to a
> person, this way you can only store a single person's data in the table.
>  Is that really your intention?

I could see this if you were storing translations there (although
that would require a third column like 'language').

I guess you could also store form labels there (so the label for a text
type input was generated via scalar_get('firstname') which may return
"First Name" - but it would be pretty terrible for real record management.

-- 
Matthew Carter (m@ahungry.com)
http://ahungry.com

[toc] | [prev] | [next] | [standalone]

#14506

"Christoph M. Becker" <cmbecker69@arcor.de> wrote in message 
news:m3gas6$i6j$1@solani.org...
> Am 06.11.2014 um 17:15 schrieb James Harris:
>> "Matthew Carter" <m@ahungry.com> wrote in message
>> news:87oaskxriy.fsf@ahungry.com...
>>> "James Harris" <james.harris.1@gmail.com> writes:
>>>
>>>>   scalar_get("forename");
>>>>   echo "<br/>";
>>>>   scalar_get("surname");
>>>>   echo "<br/>";
>>>
>>> Is your database going to be two column like this?
>>>
>>>    --------------
>>>    | id | value |
>>>    --------------
>>
>> The table containing the scalars will be exactly like that.
>
> I'm rather confused.  Assuming that "forename" and "surname" refer to a
> person, this way you can only store a single person's data in the table.
> Is that really your intention?

The keys "forename" and "surname" were just the first thing that came into 
my head as test data so I had something to emit to the page to check that my 
code was working. Real keys/identifiers/labels (call them what you will) for 
the scalars mentioned in the scalar_get() function will just be arbitrary 
pieces of text.

James

[toc] | [prev] | [next] | [standalone]

#14507

James Harris wrote:

> "Christoph M. Becker" <cmbecker69@arcor.de> wrote in message 
>> I'm rather confused.  Assuming that "forename" and "surname" refer to a
>> person, this way you can only store a single person's data in the table.
>> Is that really your intention?
> 
> The keys "forename" and "surname" were just the first thing that came into 
> my head as test data so I had something to emit to the page to check that my 
> code was working. Real keys/identifiers/labels (call them what you will) for 
> the scalars mentioned in the scalar_get() function will just be arbitrary 
> pieces of text.

Ah, I see.  In this case a key-value store might be more appropriate
than a relational database.  Perhaps you find DBA[1] useful, or maybe
MongoDB[2] or another NoSQL database[3].

[1] <http://php.net/manual/en/book.dba.php>
[2] <http://php.net/manual/en/book.mongo.php>
[3] <http://en.wikipedia.org/wiki/NoSQL>

-- 
Christoph M. Becker

[toc] | [prev] | [next] | [standalone]

#14503

James,

Sorry, a bit long but I want to give as much detail as possible.

More below.

On 11/6/2014 10:29 AM, James Harris wrote:
> "Jerry Stuckle" <jstucklex@attglobal.net> wrote in message 
> news:m3erom$ri2$1@dont-email.me...
>> On 11/5/2014 7:23 PM, James Harris wrote:
>>> I am looking for a good way to pull ad-hoc info from a database into a 
>>> web
>>> page. By "good" I mean a way that is efficient for the server and 
>>> convenient
>>> for the person writing the web page. Some of the ad-hoc info will be
>>> scalars. Some will be lists and, possibly, some will be tabular. Is there
>>> already a standard way to do things like this?
>>>
>>> It's been a whiile since I used PHP but from a quick look it seems it may 
>>> be
>>> best to define a number of functions to retrieve and echo data. A call to
>>> the scalar function would be something like
>>>
>>>   scalar_get("key")
>>>
>>> where the key uniquely identifies a cell in a table. The subroutine would
>>> just echo the result into the output html that gets sent to the web 
>>> browser.
>>> Is that reasonable and is there a way to make the subroutine efficient 
>>> given
>>> that there may be many scalar_get() calls in the body of the page?
>>>
>>> James
>>>
>>>
>>
>> First of all, there is not "cell" in a SQL database table.  You get one
>> or more rows, and can select columns from that row.  If you have a
>> primary key, you will get a single row back by selecting on that key.
> 
> Would "field" be better? Whatever the case I mean the intersection of a row 
> and a column of a table, i.e. a specific field in a specific record where 
> the record is idenfitied by a unique key.
>

Yes, that would be better.  The problem we get here (and in SQL
newsgroups) is people new to relational databases think they are like
spreadsheets.  They really aren't.

> To illustrate what I was asking in my original post I have come up with a 
> function to retrieve scalars. It works but I am concerned that it may be 
> inefficient due to reissuing the database-connect call each time. Is there a 
> better way to write this so that the connection is opened once and just 
> reused as many times as the function is called? Would making the connection 
> a gobal be the way to do it?
> 

Definitely bad performance.  The connection should be opened once,
before the first access attempt.  It should be closed only after the
last access.

Some other comments inline:

> function scalar_get($key) {
>   global $sv, $db, $un, $pw;

Globals are bad.  If a global variable changes, you don't know what
caused the change.  You should pass anything you need as a function
parameter.  But in this case the connection should be done outside of
the function, as I indicated above, so you don't need them.  (Related
comments at the bottom).

>   echo "In function scalar_get($key)<br/>";
>   echo "Make db connection to $db<br/>";
> 
>   $conn = mysqli_connect($sv, $un, $pw);
>   if (!$conn) {
>     die("Database connection failed: " . mysqli_connect_error());
>   }
>   echo "Database connected successfully<br/>";
> 

NEVER use 'die' or 'exit' in code for a web page.  It causes immediate
termination of everything on the page, resulting in invalid HTML (at the
very least, no HTML end tags).  Rather, if a required operation does not
complete correctly, log (on a production system) or display (on a
development system) the error and bypass any code depending on the
operation's success with an 'if' statement.

>   $sql = "select value from $db.scalars where id = \"$key\";";
>   $result = mysqli_query($conn, $sql);
>   if ($result === FALSE) {
>     echo "Query ($sql) failed: " . mysqli_error($conn) . "<br/>";
>   } elseif (mysqli_num_rows($result) !== 1) {
>     echo "Expected one row, got " . mysqli_num_rows($result) . "<br/>";
>   } else {
>     echo "Got one row<br/>";
>     $row = mysqli_fetch_row($result);
>     echo "Result is ($row[0])<br/>";
>   }
> }
> 
> The idea is that a function like the above (but less verbose!) would be 
> called an arbitrary number of times in the main body of a page by means of 
> calls such as the following. At least for scalars I may get it to return the 
> string instead of printing it.
> 
>   scalar_get("forename");
>   echo "<br/>";
>   scalar_get("surname");
>   echo "<br/>";
>

I'm not sure what your database design (and needs) are, but it looks
like your table has two values - a identity and a value.  While this
type of table can be used (and I've used it before), it's not a usual
design.  Are you saying you can only have one "forename" in the table
and one "surname"?  What if you want to put two people in there?

But as to your question - with the current database design, I wouldn't
even call a function.  I'd just put the code inline, fetch everything at
once.  Something like:

if (($conn = mysqli_connect($sv, $un, $pw, $db)) !== FALSE) {
  $sql = 'SELECT id, value FROM scalars WHERE id in ('forename', 'surname');
  if (($result = mysqli_query($conn, $sql)) !== FALSE) {
     $values = array();
     while ($row = mysqli_fetch_row($result))
         $array[$row['id']] = $row['value'];
     echo isset($array['forename']) ? $array['forename'] . "\n" : '';
     echo isset($array['surname']) ? $array['forename'] . "\n" : '';
  } else {
    // Log an appropriate error message here, if desired
  }
  mysqli_close($conn);
} else {
  // Log an appropriate error message here, if desired
}

Several things here.  First of all, I open the connection once, at the
start, and close it at the end.  If you're going to have multiple calls
to the database for different information, then the mysqli_connect()
goes before the first request and mysqli_close() goes after the last
request.

I use an IN clause to get both forename and surname in one call.  There
are many ways to do this - but the most efficient way to work with a
database generally means making the fewest number of calls (there are
exceptions, but we won't go into those).  In this case since I know I'm
getting both forename and surname and are going to use them immediately,
I fetch both with one query.

Note that I also get both the id and the value fields.  This is because
without an ORDER clause, returned rows are unordered.  They MAY come
back in the same order as the values in my IN clause, but don't have to.
 So I fetch id and value, then set the array values to
$values[id]=value.  This gives me an associative array.

Next, I display the values in the order I wish, first of all ensuring
the value is set with isset() (you don't want a PHP problem because of a
missing row in a database).

Finally, I close the connection to the database.  This is done after the
last call to the database, often times near the bottom of the script.
BTW, you forgot to do this in your script, so every call to your
function would try to open a new connection.  Do this enough times and
you could run out of connections.

>> As for getting multiple rows - I normally select all rows of interest,
>> and display them in a loop.  That is much more efficient than retrieving
>> multiple rows based on the selection criteria used to retrieve the
>> primary keys.
> 
> Understood.
> 
> James
> 

Related to the globals - typically, host, userid, password and database
name are constant - they won't change within the script.  I always set
these with something like:

define('HOST', 'myhost');
define('USERID', 'myuser');
define('PASSWORD', 'mypassword');
define("DATABASE', 'mydatabase');

(using the appropriate values for each, of course).

These then go in a file which is OUTSIDE of the document root hierarchy,
for security reasons (good hosting companies give you access to a
directory above your document root, i.e. '/home/jerry/', where the
Apache document root is in '/home/jerry/html/').

Then include this file in the scripts requiring it with

require_once($_SERVER['DOCUMENT_ROOT'] . '/../config.php');

config.php is the name of the file.

Open your connection with something like:

$conn = mysqli_connect(HOST, USERID, PASSWORD, DATABASE);

This keeps passwords out of your code and adds security should something
happen like the web server get misconfigured.  It also prevents someone
from directly accessing the file through the web.  And constants are
available anywhere in the script, but cannot be changed, resulting in
fewer potential bugs.

Again, sorry for being so verbose, but I hope this helps.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex@attglobal.net
==================

[toc] | [prev] | [next] | [standalone]

#14522

On Thu, 6 Nov 2014 15:29:35 -0000, James Harris wrote:

> "Jerry Stuckle" <jstucklex@attglobal.net> wrote in message 
> news:m3erom$ri2$1@dont-email.me...
>> On 11/5/2014 7:23 PM, James Harris wrote:
>>> I am looking for a good way to pull ad-hoc info from a database into a 
>>> web
>>> page. By "good" I mean a way that is efficient for the server and 
>>> convenient
>>> for the person writing the web page. Some of the ad-hoc info will be
>>> scalars. Some will be lists and, possibly, some will be tabular. Is there
>>> already a standard way to do things like this?
>>>
>>> It's been a whiile since I used PHP but from a quick look it seems it may 
>>> be
>>> best to define a number of functions to retrieve and echo data. A call to
>>> the scalar function would be something like
>>>
>>>   scalar_get("key")
>>>
>>> where the key uniquely identifies a cell in a table. The subroutine would
>>> just echo the result into the output html that gets sent to the web 
>>> browser.
>>> Is that reasonable and is there a way to make the subroutine efficient 
>>> given
>>> that there may be many scalar_get() calls in the body of the page?
>>>
>>> James
>>>
>>>
>>
>> First of all, there is not "cell" in a SQL database table.  You get one
>> or more rows, and can select columns from that row.  If you have a
>> primary key, you will get a single row back by selecting on that key.
> 
> Would "field" be better? Whatever the case I mean the intersection of a row 
> and a column of a table, i.e. a specific field in a specific record where 
> the record is idenfitied by a unique key.
> 
> To illustrate what I was asking in my original post I have come up with a 
> function to retrieve scalars. It works but I am concerned that it may be 
> inefficient due to reissuing the database-connect call each time. Is there a 
> better way to write this so that the connection is opened once and just 
> reused as many times as the function is called? Would making the connection 
> a gobal be the way to do it?
> 
> function scalar_get($key) {
>   global $sv, $db, $un, $pw;
>   echo "In function scalar_get($key)<br/>";
>   echo "Make db connection to $db<br/>";
> 
>   $conn = mysqli_connect($sv, $un, $pw);
>   if (!$conn) {
>     die("Database connection failed: " . mysqli_connect_error());
>   }
>   echo "Database connected successfully<br/>";
> 
>   $sql = "select value from $db.scalars where id = \"$key\";";
>   $result = mysqli_query($conn, $sql);
>   if ($result === FALSE) {
>     echo "Query ($sql) failed: " . mysqli_error($conn) . "<br/>";
>   } elseif (mysqli_num_rows($result) !== 1) {
>     echo "Expected one row, got " . mysqli_num_rows($result) . "<br/>";
>   } else {
>     echo "Got one row<br/>";
>     $row = mysqli_fetch_row($result);
>     echo "Result is ($row[0])<br/>";
>   }
> }
> 
> The idea is that a function like the above (but less verbose!) would be 
> called an arbitrary number of times in the main body of a page by means of 
> calls such as the following. At least for scalars I may get it to return the 
> string instead of printing it.
> 
>   scalar_get("forename");
>   echo "<br/>";
>   scalar_get("surname");
>   echo "<br/>";
> 
>> As for getting multiple rows - I normally select all rows of interest,
>> and display them in a loop.  That is much more efficient than retrieving
>> multiple rows based on the selection criteria used to retrieve the
>> primary keys.
> 
> Understood.
> 
> James

IMNSHO, a "field" is the name of the column while a "cell" is the data
within that field. A "cell" can have data or have no data.
A "row" consists of "cells" which are identified by the "field" name.

You access the data cell by selecting a row(s).
Actually, accessing the entire table itself.
Then defining precisely what it is you want to show.

Select * From table where field=value.[1]

This act places all amtching items into an array.
Using a method such as a loop to display that data in what ever way you
desire.


[1] usage here is only for simplicity sake. Exact syntax should be
researched before using.
*="all". * can be replaced with precise field names such as field1,field2.

[toc] | [prev] | [next] | [standalone]

#14529

On Mon, 10 Nov 2014 11:17:55 -0500, richard wrote:

> IMNSHO, a "field" is the name of the column while a "cell" is the data
> within that field.

In your distorted worldview a database table is a spreadsheet, we know 
this, you've told us this before, you don't have to keep telling us.

-- 
Denis McMahon, denismfmcmahon@gmail.com

[toc] | [prev] | [standalone]

Page 2 of 2 — ← Prev page 1 [2]

Back to top | Article view | comp.lang.php

csiph-web