You are using the non-prepared query(). I thought that
$q = $conn->query("SELECT * FROM PDO");preduces a query on the SQL and
$stmt = $conn->prepare($q);
$stmt->execute();
is needed to get the data. This is code given to me by the forum phpfreaks.
https://phpdelusions.net/pdo#query
$stmt = $pdo->query('SELECT name FROM users');
while ($row = $stmt->fetch())
{
echo $row['name'] . "\n";
}I was told on another forum that ALL sql had to be prepared
SQL
Prepare
Execute
but the above example does not use prepare or execute. It dosn't matter if I use local host or not. I am after an example of the use of returning a record set.
Technically, it doesn't all have to be prepared, as the $pdo->query() method will attempt to run whatever SQL you supply it as is. But from a security standpoint (and possibly other good reasons), it's generally best to use prepared statements. In your simple example where the query is just SELECT name FROM users, there is no security risk since no external values are being used in the query. However, the moment you try to do something more likely to be done in an actual app, such as $stmt = $pdo->query("SELECT * FROM users WHERE name = '{$_GET['user_name']}');, you are now at serious risk of an SQL injection attack. Therefore, the preferred method would be more like:
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :user_name);
$stmt->execute([:user_name => $_GET['user_name']]);
while ($row = $stmt->fetch()) {
// do something with each result row
}
Okay. I am using the code supplied on the website.
https://phpdelusions.net/pdo#query
I am using a server with PHP5.4 is that the reason? PDO does not work on this version.
Error: Fatal error: Cannot use object of type stdClass as array in /vhost/vhost15/d/e/s/desmond-otoole.co.uk/www/music/music.php on line 43
<?php
error_reporting(E_ALL);
ini_set('display_errors', '1');
define ('HOSTNAME1', 'mysql09.iomart.com');
define ('USERNAME1', 'otoogc692');
define ('PASSWORD1', 'mauritius');
define ('DATABASE1', 'otoogc692');
function ConnectDB()
{
static $conn = null;
if ($conn === null)
{
$host = HOSTNAME1;
$user = USERNAME1;
$pass = PASSWORD1;
$MyDB = DATABASE1;
$conn = new PDO("mysql:host=$host; dbname=$MyDB; charset=UTF8", $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
]);
}
return $conn;
}
?>
<html>
<body>
<?
echo "Hi There here is the list!!!";
$conn = connectDB();
$stmt = $conn->query("SELECT * FROM PDO");
//$stmt = $conn->prepare($q);
//$stmt->execute();
// What is stmt?
while ($row = $stmt->fetch())
{
echo $row['First_N'] . "<br/>";
}
?>
</body>
</html>Your code
$stmt = $pdo->prepare("SELECT * FROM users WHERE name = :user_name);
$stmt->execute([:user_name => $_GET['user_name']]);
while ($row = $stmt->fetch()) {
// do something with each result row
Replace // do something with each result row with
echo $row['First_N'] . "<br/>";
// Cannot use object of type stdClass as array
}The error has nothing to do with php version. You are fetching the data as an object, but attempting to use array syntax to access it.
You were already told elsewhere what to set the default fetch mode to -
and set the default fetch mode to assoc, you are setting it to obj, which is not what you are using in the example code and is probably not what your existing code is using with the fetched data, which will require you to make even more changes to your existing code.
Programming is a writing and reading activity. You must at some point actually read the words and syntax making up the code you are using and be able to describe to yourself what they are doing.
otuatail PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJ
Even if you have no specific experience with php or the PDO extension, doesn't reading the above line of code hint that you are setting something called the default fetch mode to OBJ (an abbreviation of the word object)?
otuatail I was told on another forum that ALL sql had to be prepared
SQL
Prepare
Execute
If you aren't using any placeholders to supply variables to the query, then pdo_query does both the preparation and execution steps immediately. If there aren't any placeholders in the SQL statement then there's nothing to do between preparation and execution so they don't need to be split into two steps. This is why the manual page for pdo_query says that the method "prepares and executes an SQL statement without placeholders".
otuatail I am using a server with PHP5.4 is that the reason? PDO does not work on this version.
No, PDO has been around since 5.1.
Yes, because you're telling your PDO connection to return results as objects instead of arrays. That is what the
PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_OBJsetting you've used does.
See PDO constants for a list of the other FETCH_* options available. Some of them make PDO give you arrays, some give you objects.
Yes you are correct. FETCH_ASSOC. I did try this. and this did not work. TRhis results in crashing the web page with
This page isn’t working
function ConnectDB()
{
static $conn = null;
if ($conn === null)
{
$host = HOSTNAME1;
$user = USERNAME1;
$pass = PASSWORD1;
$MyDB = DATABASE1;
$conn = new PDO("mysql:host=$host; dbname=$MyDB; charset=UTF8", $user, $pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
PDO::ATTR_DEFAULT_FETCH_MODE => FETCH_ASSOC
]);
}
return $conn;
}
?>
<html>
<body>
<?echo "This list isnt working";
$pdo = connectDB();
$stmt = $pdo->query("SELECT * FROM PDO");
ecPDO");
while ($row = $stmt->fetch())
{
echo $row['First_N'] . "<br/>";
}
?>I have been there.
Notice: Use of undefined constant FETCH_ASSOC - assumed 'FETCH_ASSOC
Does $row['Name_F'] have to be an index as I don't use indexes in this table?
otuatail TRhis results in crashing the web page with
This page isn’t working
That will always be printed because you put it as the first thing the page should do, before you've even attempted to connect to the datatabase.
I'm also guessing that what you're posting isn't actually what you're running, based on the spurious text in
$stmt = $pdo->query("SELECT * FROM PDO");
ecPDO");
while ($row = $stmt->fetch())otuatail
No, but First_N has to be a column in the table you're querying.
Not now. I did change the names in the table.
Notice: Undefined index: Name_F in /vhost/vhost15/d/e/s/desmond-otoole.co.uk/www/music/music.php on line 37
Field Type
Name_F varchar(20)
Name_S varchar(20)
DOB date
<?
$pdo = connectDB();
$stmt = $pdo->query("SELECT * FROM PDO");
while ($row = $stmt->fetchAll())
{
echo $row['Name_F'] . "<br/>";
}
?>DROP TABLE IF EXISTS `PDO`;
CREATE TABLE IF NOT EXISTS `PDO` (
`Name_F` varchar(10),
`Name_S` varchar(10),
`DOB` date
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
INSERT INTO `PDO` VALUES
('Bruce' , 'Springsteen', '1946-09-05'),
('Feddy' , 'Mercury', '1946-09-05'),
('Bruce' , 'Willis', '1955-03-19'),
('Marvin' , 'Gaye', '1939-04-02'),
('Diana' , 'Ross', '1944-03-26');
[/code']<?
$pdo = connectDB();
$stmt = $pdo->query("SELECT * FROM PDO");
while ($row = $stmt->fetchAll())
{
echo $row['Name_F'] . "<br/>";
}
?>Notice: Undefined index: Name_F in /vhost/vhost15/d/e/s/desmond-otoole.co.uk/www/music/music.php on line 37
So execution is getting that far: rows are being returned, but something is wrong with the contents of those rows. Do some debugging: replace the echo with a simple var_dump($row) to see what exactly it contains.
(Incidentally, MyISAM isn't appropriate in most situations for safety reasons, and mixing character encodings (UTF8 in one place, latin1 in another) is a good way to corrupt text. And you've misspelled "Freddie" 
).
$row = $stmt->fetchAll()
The fetchAll() method returns an array consisting of all the rows in the result set, so of course there's no main index named Name_F.
Oh yeah, I'm so used to using foreach to iterate over a collection that I completely missed that it said fetchAll and not fetch. It used to say fetch but the OP changed that for whatever reason.