I'm trying to automatically populate a navigation menu based on available categories in the database. As follows is my code:
<?php
$sidenav = query("SELECT cat_id, cat_name FROM prod_cat ORDER By cat_name LIMIT 20");
if ($sidenav != false)
{
$sidenav->execute();
while($result = $sidenav->fetch(PDO::FETCH_BOTH))
{
echo "<div class='left_button'><a href='/customcms/products/index.php?page=list-product&category={$result['cat_id']}'>{$result['cat_name']}</a></div>";
}
}
?>
However, the code "$sidenav->execute();" gives the following error message: "Fatal error: Call to a member function execute() on a non-object on line ...".
Note: Please, be advised that "query" is a custom function and I'm connecting to the database via PDO.
I have a custom function called "query" with PDO statement in the functions.php file:
/**
* Executes SQL statement, possibly with parameters, returning
* an array of all rows in result set or false on (non-fatal) error.
*/
function query(/* $sql [, ... ] */)
{
// SQL statement
$sql = func_get_arg(0);
// parameters, if any
$parameters = array_slice(func_get_args(), 1);
// try to connect to database
static $handle;
if (!isset($handle))
{
try
{
// connect to database
$handle = new PDO("mysql:dbname=" . DB_NAME . ";host=" . DB_SERVER, DB_USERNAME, DB_PASSWORD);
// ensure that PDO::prepare returns false when passed invalid SQL
$handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch (Exception $e)
{
// trigger (big, orange) error
trigger_error($e->getMessage(), E_USER_ERROR);
exit;
}
}
// prepare SQL statement
$statement = $handle->prepare($sql);
if ($statement === false)
{
// trigger (big, orange) error
trigger_error($handle->errorInfo()[2], E_USER_ERROR);
exit;
}
// execute SQL statement
$results = $statement->execute($parameters);
// return result set's rows, if any
if ($results !== false)
{
return $statement->fetchAll(PDO::FETCH_ASSOC);
}
else
{
return false;
}
}
I'm trying to rewrite/update my old script. Now, how do I use the above to modify the following old mysql_query and mysql_num_rows statements I have in my old script?:
$sql = "SELECT al_id, al_name, al_image, COUNT(im_album_id) AS al_numimage
FROM tbl_album al LEFT JOIN tbl_image im ON al.al_id = im.im_album_id
GROUP by al_id
ORDER BY al_name";
$result = mysql_query($sql) or die('Error, list product category failed. ' . mysql_error());
if (mysql_num_rows($result) == 0) {
echo "No product category yet";
}
Finally, I've been able to get the script to work. As follows is the working code:
<?php
if (isset($_GET['delete']) && isset($_GET['album']) && isset($_GET['imgId'])) {
//$albumId = mysql_real_escape_string($_GET['album']);
//$imgId = mysql_real_escape_string($_GET['imgId']);
$imgId = (int)$_GET['imgId'];
// Get the data to unlink:
$qry = sprintf('SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = %d', $imgId);
$result = mysql_query($qry) or die('Unable to retrieve image information ' . mysql_error());
if ($row = mysql_fetch_assoc($result))
{
// Since im_id is a primary key, you can only ever have zero or one records with the query above
unlink(GALLERY_IMG_DIR . $row['im_image']);
unlink(GALLERY_IMG_DIR . 'thumbnail/' . $row['im_thumbnail']);
$delQry = sprintf('DELETE FROM tbl_image WHERE im_id = %d', $imgId); // no need for albumid as an img_id is unique
$delResult = mysql_query($delQry) or die('Unable to delete record: ' . mysql_error());
}
}
$imagePerPage = 10;
$album = isset($_GET['album']) ? $_GET['album'] : '';
$pageNumber = isset($_GET['pageNum']) ? $_GET['pageNum'] : 1;
$offset = ($pageNumber - 1) * $imagePerPage;
$serial = $offset + 1;
// get album list
$sql = "SELECT al_id, al_name
FROM tbl_album
ORDER BY al_name";
$result = mysql_query($sql) or die('Error, get product category list failed : ' . mysql_error());
$albumList = '';
while ($row = mysql_fetch_assoc($result)) {
$albumList .= '<option value="' . $row['al_id'] . '"' ;
if ($row['al_id'] == $album) {
$albumList .= ' selected';
}
$albumList .= '>' . $row['al_name'] . '</option>';
}
?>
@cereal, I'm overwhelmed by your devotion and patience in enlightening me. I so much appreciate that.
Strangely enough, any attempt to delete any image in the desired database row doesn't work. This issue is too confusing to me. I just don't know why your code shouldn't work - it is cleaner and more professional.
if you expect to set any of those values then my suggested conditional statement will fail and your query will not work. So, if any of these are expected let us know so we can suggest an appropriate solution.
I'm not sure if any of those values should be set. I simply want to click on the "Delete" link next to any chosen image and the database row containing the image would be deleted.
The following is the JavaScript function I have in the index.php file that effects the deletion - Please, look into it too:
function deleteImage(albumId, imgId)
{
if (confirm('Delete this product?')) {
window.location.href = 'index.php?page=list-image&delete&album=' + albumId + '&imgId=' + imgId;
}
}
When I dumped the $sql var, I got this error message:
Error
SQL query: Documentation
$sql = "SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = '$imgId' AND im_album_id = '$albumId' ";
MySQL said: Documentation
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '$sql = "SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = '$imgId' AND ' at line 1
When I inspected the source code of the "Delete" link for one image, I noticed that only its im_id was present where 13 is the im_id, while im_album_id was empty:
@diafol, I meant that the first select query in the file generates syntax error, when I run the SQL query on PHPMyAdmin. Both queries given below, which I've tried give similar error messages:
$sql = "SELECT im_image, im_thumbnail
FROM tbl_image
WHERE im_id = {$_GET['imgId']} AND im_album_id = {$_GET['album']}";
$result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error());
And this variation too:
$sql = "SELECT im_image, im_thumbnail
FROM tbl_image
WHERE im_id = $imgId AND im_album_id = $albumId");
$result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error());
Please, can you write the correct query statement for replacement?
I'm still not able to fix the issue. Is there a better way to write this select querry?
$sql = "SELECT im_image, im_thumbnail
FROM tbl_image
WHERE im_id = {$_GET['imgId']} AND im_album_id = {$_GET['album']}";
$result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error()
I've changed the code to the following, but it didn't work:
$albumId = mysql_real_escape_string($_GET['album']);
$imgId = mysql_real_escape_string($_GET['imgId']);
// get the image file name so we
// can delete it from the server
echo ($sql = "SELECT im_image, im_thumbnail
FROM tbl_image
WHERE im_id = $imgId AND im_album_id = $albumId");
$result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error());
I echoed the MySQL query and got the following error message:
SELECT im_image, im_thumbnail FROM tbl_image WHERE im_id = 1 AND im_album_id = Delete product category failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
It also didn't work in PHPMyAdmin:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 2
Still PHPMyAdmin:
SELECT im_image, im_thumbnail
FROM tbl_image
WHERE im_id =1
AND im_album_id =
LIMIT 0 , 30
I guess the problem is coming from im_album_id that is empty. If that is the case, how do I fix it?
I'm experiencing an issue deleting data via PHP/MySQL query. The following is the error message:
Delete product category failed. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
This is the PHP code that I'm using:
<?php
if (isset($_GET['delete']) && isset($_GET['album']) && isset($_GET['imgId'])) {
// get the image file name so we
// can delete it from the server
$sql = "SELECT im_image, im_thumbnail
FROM tbl_image
WHERE im_id = {$_GET['imgId']} AND im_album_id = {$_GET['album']}";
$result = mysql_query($sql) or die('Delete product category failed. ' . mysql_error());
if (mysql_num_rows($result) == 1) {
$row = mysql_fetch_assoc($result);
// remove the image and the thumbnail from the server
unlink(GALLERY_IMG_DIR . $row['im_image']);
unlink(GALLERY_IMG_DIR . 'thumbnail/' . $row['im_thumbnail']);
// and then remove the database entry
$sql = "DELETE FROM tbl_image
WHERE im_id = {$_GET['imgId']} AND im_album_id = {$_GET['album']}";
mysql_query($sql) or die('Delete product category failed. ' . mysql_error());
}
}
$imagePerPage = 10;
$album = isset($_GET['album']) ? $_GET['album'] : '';
$pageNumber = isset($_GET['pageNum']) ? $_GET['pageNum'] : 1;
$offset = ($pageNumber - 1) * $imagePerPage;
$serial = $offset + 1;
// get album list
$sql = "SELECT al_id, al_name
FROM tbl_album
ORDER BY al_name";
$result = mysql_query($sql) or die('Error, get product category list failed : ' . mysql_error());
$albumList = '';
while ($row = mysql_fetch_assoc($result)) {
$albumList .= '<option value="' . $row['al_id'] . '"' ;
if ($row['al_id'] == $album) {
$albumList .= ' selected';
}
$albumList .= …
I wrote a modify password file, but unfortunately, the MySQL query is throwing error:
Warning: mysql_query() expects at most 2 parameters, 3 given in admin\modify-password.php on line 40
This is the PHP code:
if (mysql_query("UPDATE admin SET hash = ? WHERE admin_id = ?", crypt($_POST["newpassword"]), $_SESSION["admin_id"]) === false)
I modified the code like this, and it produced a different error message:
if (mysql_query("UPDATE admin SET hash = ? WHERE username = 'admin'", '$newpassword') === false)
The second error message:
Warning: mysql_query() expects parameter 2 to be resource, string given in admin\modify-password.php on line 40
Here is the full code:
<?php
// if form was submitted
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
//This gets all the other information from the form
$curpassword = mysql_real_escape_string(crypt($_POST["curpassword"]));
$newpassword = mysql_real_escape_string(crypt($_POST["newpassword"]));
$confirmation = mysql_real_escape_string(crypt($_POST["confirmation"]));
// validate submission
if (empty($_POST["curpassword"]))
{
echo nl2br ("You must provide your current password. \n");
}
if (empty($_POST["newpassword"]))
{
echo nl2br ("You must enter a desired new password. \n");
}
if (empty($_POST["confirmation"]))
{
echo nl2br ("You must confirm your new password. \n");
}
// query database for admin
$rows = mysql_query("SELECT * FROM admin WHERE username = 'admin'");
//$row = $rows[0];
// compare hash of user's input against hash that's in database
if (crypt($_POST["curpassword"], $row["hash"]) != $row["hash"])
{
echo nl2br ("Your input and your current password don't match. \n");
}
if ($_POST["newpassword"] != $_POST["confirmation"])
{
echo nl2br ("Your new password and confirmation don't match. \n");
}
// update the admin's password to the new one
if (mysql_query("UPDATE admin …
I took a look at the code snippet you linked to, but was confused by the fact that it mixed connection with the query in the same file.
Please, what the best way to write the following using MySQLi and also preventing SQL injection?
$query = "INSERT INTO admin (username, hash, email) VALUES (?, ?, ?),
$username,
$password,
$email";
// if username is in database
mysql_query($query) or die('Sorry, an error occurred while inserting data into the database. ' . mysql_error());
I'm getting the following error message, while inserting data into a MySQL database table:
Sorry, an error occurred while inserting data into the database. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?, ?, ?), admin, $1$2W/.hV3.$3iwUzDrlU4BvNPp80vy8J0, ' at line 1
The following is my PHP code:
<?php
// include configuration file
require ("../library/config.php");
require_once '../library/functions.php';
// if form was submitted
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
// validate submission
if (empty($_POST["username"]))
{
echo nl2br ("Provide a username. \n");
}
else if (empty($_POST["password"]))
{
echo nl2br ("Enter a password. \n");
}
else if (empty($_POST["confirmation"]))
{
echo nl2br ("Confirm your password. \n");
}
else if ($_POST["password"] != $_POST["confirmation"])
{
echo ("Password and confirmation do not match. \n");
}
if (empty($_POST["email"]))
{
echo ("Provide your email address. \n");
}
if (!empty($_POST["username"]))
{
//This gets all the other information from the form
$username = $_POST["username"];
$password = crypt($_POST["password"]);
$email = $_POST["email"];
// validate username
$username = ($_POST["username"]);
if (!preg_match("/^[a-zA-Z0-9]*$/", $username))
{
echo "Username must contain only letters and numbers.";
}
if (strlen($username) < 4 || strlen($username) > 10)
{
echo "Username must be from 4 to 10 characters.";
}
// validate email address
$email = ($_POST["email"]);
if (!preg_match("/([\w\-]+\@[\w\-]+\.[\w\-]+)/", $email))
{
echo "Invalid email address.";
}
if ($_POST["email"] === false)
{
echo "The email has already been taken.";
}
//$query = "INSERT INTO admin (username, hash, email) VALUES ($username, $password, $email)";
$query = "INSERT INTO admin (username, …
I have a user registration script that includes image upload. Everything works, but each time a user registers, the image he/she uploads replaces the image (userimage) of every other user. What I need is to only update the userimage of that user based on ID. Any help would be much appreciated. Here is my code:
<?php
// configuration
require("../../includes/config.php");
// query users table to retrieve admin homepage's contents
// $users = query("SELECT * FROM users WHERE id = ?");
//Class import for image uploading
//classes is the map where the class file is stored (one above the root)
include ("../../classes/upload/upload_class.php");
$id = $_GET["id"];
$users = query("SELECT * FROM users WHERE id = '$id'");
// if form was submitted, modify user
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
// validate submission
if (empty($_POST["firstname"]))
{
adminapologize("Provide your first name.");
}
if (empty($_POST["lastname"]))
{
adminapologize("Provide your last name.");
}
if (empty($_POST["username"]))
{
adminapologize("Provide a username.");
}
if (empty($_POST["usersex"]))
{
adminapologize("Select your sex.");
}
else if (empty($_POST["password"]))
{
adminapologize("Enter a password.");
}
else if (empty($_POST["confirmation"]))
{
adminapologize("Confirm your password.");
}
else if ($_POST["password"] != $_POST["confirmation"])
{
adminapologize("Password and confirmation do not match.");
}
if (empty($_POST["email"]))
{
adminapologize("Provide your email address.");
}
if (empty($_POST["phone"]))
{
adminapologize("Enter your phone number.");
}
//This is the directory where images will be saved
$max_size = 1024*250; // the max. size for uploading
$my_upload = new file_upload;
$my_upload->upload_dir = "../images/user/"; // "files" is the folder for the uploaded files (you have to create this folder)
$my_upload->extensions = array(".png", ".gif", ".jpeg", ".jpg"); // …
I still haven't solved the issue. Can anyone help?
The data I want to display as the input values are firstname, lastname, username, usersex, password, email, phone and userimage. These are data of any registered user already in the database.
There is a web page called list-user.php that correctly lists all the registered users and their associated data in corresponding rows. And in each user's row, the user's ID is hyperlinked with this:
Clicking the "Modify" link of any ID on list-users.php takes the admin to modify-user.php. I want the selected user's data to auto-populate form inputs on modify-user.php, so the admin can edit them. But unfortunately, the form inputs are empty.
That's very surprising. before I've used that exact same function in another script to auto-populate a form input and edit it. The only difference was that in the previous case, I logged in as a regular user and was able to auto-populate my account and edit it. But in this case, I log in as the site's admin and I need to be able to auto-populate any user's account I select its ID and edit it.
Here is my functions.php inside which is the query function:
<?php
/**
* functions.php
*
* Ovi Charity
*
* Helper functions.
*/
require_once("constants.php");
/**
* Apologizes to user with message.
*/
function apologize($message)
{
render("apology.php", ["message" => $message]);
exit;
}
/**
* Apologizes to admin with message.
*/
function adminapologize($message)
{
adminrender("apology.php", ["message" => $message]);
exit;
}
/**
* Facilitates debugging by dumping contents of variable
* to browser.
*/
function dump($variable)
{
require("../templates/dump.php");
exit;
}
/**
* Logs out current user, if any. Based on Example #1 at
* http://us.php.net/manual/en/function.session-destroy.php.
*/
function logout()
{
// unset any session variables
$_SESSION = [];
// expire cookie
if (!empty($_COOKIE[session_name()]))
{
setcookie(session_name(), "", time() - 42000);
}
// destroy session
session_destroy();
}
/**
* Executes SQL statement, possibly with parameters, returning
* an array of all rows in result set or false on (non-fatal) error.
*/
function query(/* $sql [, ... ] */)
{
// SQL statement
$sql = func_get_arg(0);
// parameters, if any
$parameters = array_slice(func_get_args(), 1);
// try to connect to …
Sorry, I posted MySQL select query, instead of the query function. Here it is and it works well:
function query(/* $sql [, ... ] */)
{
// SQL statement
$sql = func_get_arg(0);
// parameters, if any
$parameters = array_slice(func_get_args(), 1);
// try to connect to database
static $handle;
if (!isset($handle))
{
try
{
// connect to database
$handle = new PDO("mysql:dbname=" . DB_NAME . ";host=" . DB_SERVER, DB_USERNAME, DB_PASSWORD);
// ensure that PDO::prepare returns false when passed invalid SQL
$handle->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
}
catch (Exception $e)
{
// trigger (big, orange) error
trigger_error($e->getMessage(), E_USER_ERROR);
exit;
}
}
I strongly believe the query function works like magic. My only problem here is aoto-populating form fields with database table data, so I can edit them.
@veedeoo, I'm using our company's custom-made appliance installed inside VMWare Player.
@pritaeas, Your idea seems to help figure out the problem. Using printf, I noticed that selecting any user's ID prints out 0, regardless of whether I select modify-user.php?id=1 or modify-user.php?id=2. I guess getting the script to associate the selected ID with its curresponding user's row in the database would be key to solving the issue.
The following is how I queried the database to select a particular user's row:
$user = query("SELECT * FROM users WHERE id = ?");
I'm aware there's an error, but I don't know how to fix it. Actually, I have another page named "list-users.php. On this page, all the users are properly listed with each user ID hyperlinked. On clicking a user's hyperlinked ID takes the admin to "modify-user.php".
@pritaeas, Thanks for replying. I need the form fields to populate with data from database table named "users". But unfortunately, they're not populated when the page (modify-user.php) loads.
I have some data in a MySQL table, and I want to use them to populate form fields, so the site's admin can edit them. This is what I have so far in modify.php, but it's not working:
<?php
// configuration
require("../../includes/config.php");
// query users table to retrieve admin homepage's contents
// $users = query("SELECT * FROM users WHERE id = ?");
//Class import for image uploading
//classes is the map where the class file is stored (one above the root)
include ("../../classes/upload/upload_class.php");
$id = $_GET["id"];
$getuser = query("SELECT * FROM users WHERE id = '$id'");
// associative array
$rows = mysqli_fetch_array($getuser, MYSQLI_ASSOC);
// if form was submitted, modify user
if ($_SERVER["REQUEST_METHOD"] == "POST")
{
if ($rows == true)
{
//This gets all the other information from the form
$firstname = $_POST["firstname"];
$lastname = $_POST["lastname"];
$username = $_POST["username"];
$usersex = $_POST["usersex"];
$password = crypt($_POST["password"]);
$email = $_POST["email"];
$phone = $_POST["phone"];
$userimage = ($_FILES["userimage"]["name"]);
}
// validate submission
if (empty($_POST["firstname"]))
{
apologize("Provide your first name.");
}
if (empty($_POST["lastname"]))
{
apologize("Provide your last name.");
}
if (empty($_POST["username"]))
{
apologize("Provide a username.");
}
if (empty($_POST["usersex"]))
{
apologize("Select your sex.");
}
else if (empty($_POST["password"]))
{
apologize("Enter a password.");
}
else if (empty($_POST["confirmation"]))
{
apologize("Confirm your password.");
}
else if ($_POST["password"] != $_POST["confirmation"])
{
apologize("Password and confirmation do not match.");
}
if (empty($_POST["email"]))
{
apologize("Provide your email address.");
}
if (empty($_POST["phone"]))
{
apologize("Enter your phone number.");
}
//This is the directory where images will be saved
$max_size = 1024*250; // the max. size for uploading …
Sorry, but I don't know how to do InnoDb transactions or MySQLi's multi_query. Can you please, guide me on how to achieve InnoDB transactions? I'd appreciate that.
Thanks for replying. query() is a custom function that executes SQL statement, possibly with parameters, returning an array of all rows in result set or false on (non-fatal) error.
