MYSQL beginner. Need help understanding script! plz help!!

[Paul_help_]

Hi guys.

I am currently reading from a php book, and I am in need of some help.

Here is an online link to the book:

http://www.cs.karelia.ru/~musen/php/2013/Books/Beginning%20PHP%205.3%20by%20Matt%20Doyle.pdf

Plz don't worry. This isn't a virus. Its totally safe to view. Its just an online pdf version of a book i am reading.

The specific pages (in the book) I need help with are between pages: 385 - 400.

I could give all of the code, below, but there is quite a lot in total (about 5/6 separate docs, that make up the application, in total). Each document is relatively small, so the problem I am having isn't as bad as it sounds.

The code creates a 'member record viewer'.

Basically, I am just trying to generally read through the code and understand it.
I am in the process of trying to understand the code on page: 390 and 391. The specific document is "member.class.php".

Now, the problem I am having is in relation to the method named: getMembers(). Not to confuse it with the other method - getMember()

The snippet of code I do not understand in getMembers() is:

$row = $st- > fetch();

in the book, it explains:

Once the array of Member objects has been created, the method runs another query. Remember the SQL_
CALC_FOUND_ROWS keyword in the original query? To extract the calculated total number of rows, you
need to run a second query immediately after the original query:

$st = $conn- > query( “SELECT found_rows() AS totalRows” );
$row = $st- > fetch();

The query calls the MySQL found_rows() function to get the calculated total, and returns the result as
an alias, totalRows . Notice that this is a regular query that uses PDO::query() , rather than a prepared
statement as used by the first query. You don ’ t need to use placeholders because the query doesn ’ t need
to contain any passed - in values; hence there is no need to go to the trouble of creating a prepared
statement.

At no point does the book ever explain what this does or what value it holds:

$row = $st- > fetch();

Can someone help?

Paul.

[Weedpacket]

Possibly the author expects you to use the manual now and then.
[man]mysqli-stmt.fetch[/man]
Turns out it fetches a row of results from the statement.

[Paul_help_]

But the thing that is confusing me is - what value does it hold?

Does it just hold an integer? Or does it hold an actual proper row of data? eg - first name, last name, etc?

Because it doesn't make any sense for the script to just hold any old row from the database.

This is where the author does not explain things properly.

your my only hope to understanding this.

Paul.

[Paul_help_]

below is the page of script I am having trouble understanding (but the whole application script is in the book - linked above, between page 385 - 400):

I understand most of the below. The only thing I do not understand is my question above. However, there is another little thing I need clarifying (as well as my question above). In the below code, the variable: $st initially holds this value:

$st = $conn- > prepare( $sql );

... this creates a PDO connection to the database.

further down in the script, you can see that the value of $st changes to:

$st = $conn- > query( “SELECT found_rows() AS totalRows” );

..... the thing I need to ask is: am I correct in believing that, the first instance of $st connects to the database and specific table, and because we are using a PDO object - the initial value of $st has been saved in the PDO odject, which makes it ok to change the value of $st to:

$st = $conn- > query( “SELECT found_rows() AS totalRows” );

.... because connection to the database and the table have been made and saved in the PDO? Am I correct to believe this?

< ?php
require_once “DataObject.class.php”;
class Member extends DataObject {
protected $data = array(
“id” = > “”,
“username” = > “”,
“password” = > “”,
“firstName” = > “”,
“lastName” = > “”,
“joinDate” = > “”,
“gender” = > “”,
“favoriteGenre” = > “”,
“emailAddress” = > “”,
“otherInterests” = > “”
);
private $_genres = array(
“crime” = > “Crime”,
“horror” = > “Horror”,
“thriller” = > “Thriller”,
“romance” = > “Romance”,
“sciFi” = > “Sci-Fi”,
“adventure” = > “Adventure”,
“nonFiction” = > “Non-Fiction”
);
public static function getMembers( $startRow, $numRows, $order ) {
$conn = parent::connect();
$sql = “SELECT SQL_CALC_FOUND_ROWS * FROM “ . TBL_MEMBERS . “ ORDER BY
$order LIMIT :startRow, :numRows”;
try {
$st = $conn- > prepare( $sql );
$st- > bindValue( “:startRow”, $startRow, PDO:😛ARAM_INT );
$st- > bindValue( “:numRows”, $numRows, PDO:😛ARAM_INT );
$st- > execute();

$members = array();
foreach ( $st- > fetchAll() as $row ) {
$members[] = new Member( $row );
}
$st = $conn- > query( “SELECT found_rows() AS totalRows” );
$row = $st- > fetch();
parent::disconnect( $conn );
return array( $members, $row[“totalRows”] );
} catch ( PDOException $e ) {
parent::disconnect( $conn );
die( “Query failed: “ . $e- > getMessage() );
}
}
public static function getMember( $id ) {
$conn = parent::connect();
$sql = “SELECT * FROM “ . TBL_MEMBERS . “ WHERE id = :id”;
try {
$st = $conn- > prepare( $sql );
$st- > bindValue( “:id”, $id, PDO:😛ARAM_INT );
$st- > execute();
$row = $st- > fetch();
parent::disconnect( $conn );
if ( $row ) return new Member( $row );
} catch ( PDOException $e ) {
parent::disconnect( $conn );
die( “Query failed: “ . $e- > getMessage() );
}
}
public function getGenderString() {
return ( $this- > data[“gender”] == “f” ) ? “Female” : “Male”;
}
public function getFavoriteGenreString() {
return ( $this- > genres[$this- > data[“favoriteGenre”]] );
}
}
?>

[sneakyimp]

Paul help!;11051671 wrote:

$st = $conn- > prepare( $sql ); 

... this creates a PDO connection to the database.

No it doesn't. That statement takes your SQL string and turns it into a data object into which you can merge some values. See PDO:😛repare

Paul help!;11051671 wrote:

..... the thing I need to ask is: am I correct in believing that, the first instance of $st connects to the database and specific table, and because we are using a PDO object - the initial value of $st has been saved in the PDO odject, which makes it ok to change the value of $st to:

No, the first one does not connect to the database. This line is probably the one that connects to the database:

$conn = parent::connect();

From what I can tell, your code is connecting to the database once and then running a few distinct queries with that one db connection. $conn is your database connection. $st gets redefined as you run different queries and probably provides a handle that you can use to access the results of the query.

[Paul_help_]

Thanks for the help guys.

Can someone help me out with the rest my questions (above).

I still need to know the answer to my reply I sent above:

But the thing that is confusing me is - what value does it hold?

Does it just hold an integer? Or does it hold an actual proper row of data? eg - first name, last name, etc?

Because it doesn't make any sense for the script to just hold any old row from the database.

This is where the author does not explain things properly.

your my only hope to understanding this.

Paul.

[laserlight]

You are using PDO not MySQLi, so the correct entry in the PHP manual to read is: PDOStatement::fetch. Reading that entry, you will find that "the return value of this function on success depends on the fetch type". The entry also says that the default is PDO::FETCH_BOTH. From there you can figure out what is returned.

Besides this PHP manual entry, you can also make use of [man]print_r[/man].

[Paul_help_]

I know that it is a 'big ask', but could you take a look in the book I have given a link to (and page number to) above, and tell me what value it holds? because to me it looks like it holds any old standard row from the table (which doesn't make sense).

I also need to ask, what is the effect of:

PDO::FETCH_BOTH

[laserlight]

Paul help! wrote:

I know that it is a 'big ask', but could you take a look in the book I have given a link to (and page number to) above, and tell me what value it holds?

Yes, I could, but no, I won't. You can easily use print_r to find out yourself. In fact, you should go ahead and use print_r even after reading the rest of this post: it would be a good learning experience.

Paul help! wrote:

because to me it looks like it holds any old standard row from the table (which doesn't make sense).

Let's review the code:

$st = $conn->query("SELECT found_rows() AS totalRows");
$row = $st->fetch();

So, the result row obtained from executing the query and fetching a row from the result set does not correspond to any row in the table. Rather, it has a single field, i.e., the result of found_rows().

Paul help! wrote:

I also need to ask, what is the effect of:

PDO::FETCH_BOTH

You would not need to ask this if you had read the PHP manual entry on PDOStatement::fetch that I linked to in my previous post. Read it. Have you read it yet? No? Read the PHP manual entry on PDOStatement::fetch in its entirety.

[sneakyimp]

laserlight is right. [man]print_r[/man] and [man]var_dump[/man] are extremely useful for finding out what variables represent. You should be experimenting with this code by running it yourself. If you are just reading the book and not playing with the code, you probably won't learn much.

[Paul_help_]

Thanks guys.

So would it just be a simple case of executing the following code (in the same document) to get the answer I need?:

print_r( $row )

Paul.

[sneakyimp]

It's hard to say it's going to give you the answer you need. It'll certainly tell you something about what exactly $row is. Try it and see what comes out.

Also, var_dump will give you more info about what's in $row. It also prints out the class names and types of objects in addition to their values. Print_r, on the other hand, can accept a second parameter which causes it to return the string it generates rather than just outputting it to stdout.

[Derokorian]

@ personally I prefer var_export, which also has a second argument to return the string instead of outputting it. Just the one I happen to like best (since its output can be used to recreate the variable in a new isolate script more easily (aka by copy pasting)).

[Bonesnap]

print_r() also prints out the class name and types of objects. Haven't used var_export() before, I'll take a look at that.

[Paul_help_]

Thanks guys.

I have just tried to do what 'Laserlight' and 'sneakyimp' recommended.

I tried to execute the following line:

print_r( $row )

... to find out what the contents of $row is.

If you take a look at my whole web page I pasted further up this thread, I can tell you that I tried executing:

print_r( $row )

...... just below this line:

$row = $st- > fetch();

..... inside the getMembers function.

and I also tried executing:

print_r( $row )

...outside the function, and still I have no luck.

what am I doing wrong guys?

Paul.

[Weedpacket]

Bonesnap;11051861 wrote:

print_r() also prints out the class name and types of objects. Haven't used var_export() before, I'll take a look at that.

Have a look at Xdebug, too.

[Paul_help_]

But could you demonstrate (ie - give me some code) to show how I would discover the contents of $row (taking my most previous question into account)?

Paul.

[Weedpacket]

You didn't say what problem you had with your previous attempts.

[Paul_help_]

In a nut shell, I cannot figure out the value of

$row;

... using:

print_r( $row );

... as I explained at the beginning of this thread, I am learning from a book and the author did not explain things very well.

A couple of you told me to discover the content of:

$row;

by using:

print_r( $row );

.... but I do not get any output. So maybe I am doing it wrong.

If someone can take a look at the code of my web page (given further up this thread), and demonstrate how I could do this (I would be grateful).

[Bonesnap]

Weedpacket;11051867 wrote:

Have a look at Xdebug, too.

I have been meaning to for a very long time, since it plays well with PhpStorm and debugging.

Paul help!;11051873 wrote:

.... but I do not get any output. So maybe I am doing it wrong.

Which means it may be an empty string or NULL. Have you tried var_dump() or var_export() on $row as well?