php where clause

ianhaney50 · October 6, 2014

Hi

I am now on to viewing listings but want to to show listings that are just submitted by that user and display them on their profile page but can't get the WHERE clause working

Below is the coding for what I have

Profile page

echo "<p><a href='view-private-listings.php?id={$_SESSION['user_id']}'>View Listings</a></p>";

Add Listing page HTML

Submitted By: <input type="text" name="submittedby">

View Listings Page

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?php 
$title = "Private Seller Listings";

include ( 'includes/header.php' );
?>

<?php
require_once("functions.php");

$con=mysqli_connect("host","username","password","database");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Start a session for error reporting
session_start();

$submittedby=$_POST['submittedby'];
$listingtitle=$_POST['listingtitle'];

$query = mysqli_query($con,"SELECT listingtitle FROM privatelistings WHERE item LIKE '%$submittedby%'");

echo "<p>Title: {$listingtitle['listingtitle']}</p>";

mysqli_close($con);

?>

<?php include( 'includes/footer.php' ); ?>

All I get is the following error

Notice: Undefined index: listingtitle in /zacs-car-site/view-private-listings.php on line 26

Thank you in advance

Kind regards

Ian

ginerjm · October 6, 2014

your postings make no sense. How about

1 - turn on php error checking

2 - stop opening and closing php mode.

3 - put together some code that shows the entire problem, not 3 pieces that we don't know how to put together.. Be sure to show us the form code and point out to us which is line 26

cyberRobot · October 6, 2014

Try changing this:

echo "<p>Title: {$listingtitle['listingtitle']}</p>";

To this:

echo "<p>Title: $listingtitle</p>";

Psycho · October 6, 2014

Yeah, your post doesn't make sense. You state you want to show the listing made by a user on their profile page and you show the URL for that page which includes an ID parameter on the URL string for the user ID.

Then you go on to show an input field used on the add listing page for 'submitted by". But, it is an input field, which means the user can type in any value. Shoudln't this be a hidden field using the user ID of the person submitting the listing (or get it dynamically on the processing page)?

Then your page to display listings is apparently trying to find listing by the user submitted value for "submitted by". I assume then that you expect the uessr to enter the name of the person who submitted the values. If it was the user's page, then you would run a page to get the listings using the User ID. This looks more like a search of listings by a user - which has nothing to do with your original request.

cyberRobot · October 6, 2014

Just to clarify my post, you are currently echoing the variable created here:

$listingtitle=$_POST['listingtitle'];

Which is why I suggested changing your echo statement. However, it looks like you're trying to get the "listingtitle" using the following query:

$query = mysqli_query($con,"SELECT listingtitle FROM privatelistings WHERE item LIKE '%$submittedby%'");

For that to work, you need to process the query results with something like mysqli_fetch_assoc(). More information can be found here:

http://php.net/manual/en/mysqli-result.fetch-assoc.php

Edited October 6, 2014 by cyberRobot

ianhaney50 · October 6, 2014

Ok sorry I understand I wasn't clear about I want to achieve

I want on the profile page of the user logged in a link that says view listings then on that page I want to show the titles of the listings that are made by that user

I have now made the submitted by input field hidden as was right, don't want the user to type that in so made it hidden

My HTML form looks like the following

<form action="private-add-insert.php" method="post" enctype="multipart/form-data">
<input type="hidden" name="submittedby">
Title: <input type="text" name="listingtitle">
Car Make: <input type="text" name="make">
Car Model: <input type="text" name="model">
Exterior Colour: <input type="text" name="exteriorcolour">
Engine Size: <input type="text" name="enginesize">
Fuel Type: <input type="text" name="fueltype">
Year Registered: <input type="text" name="yearregistered">
Transmission: <input type="text" name="transmission">
Mileage: <input type="text" name="mileage">
Number of Doors: <input type="text" name="nodoors">
Body Style: <input type="text" name="bodystyle">
Price: <input type="text" name="price">
<br>
<label>Photo1</label>
<input type='hidden' name='size' value='350000'><input type='file' name='photo[]'>
<br />
<label>Photo2</label>
<input type='hidden' name='size' value='350000'><input type='file' name='photo[]'>
<br />
<input type="submit" value="Submit Listing"> 
</form>

Below is the PHP process code for the form

<?php

ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);

// Start a session for error reporting
session_start();

$conn = mysql_connect('host','username','password', 3306) or die(mysql_error());

$db_name = mysql_select_db('databasename') or die(mysql_error());

//This is the directory where images will be saved
$target = "private-listing-images/";

//This gets all the other information from the form
$submittedby = $_POST['submittedby'];
$listingtitle = $_POST['listingtitle'];
$make = $_POST['make'];
$model = $_POST['model'];
$exteriorcolour = $_POST['exteriorcolour'];
$enginesize = $_POST['enginesize'];
$fueltype = $_POST['fueltype'];
$yearregistered = $_POST['yearregistered'];
$transmission = $_POST['transmission'];
$mileage = $_POST['mileage'];
$nodoors = $_POST['nodoors'];
$bodystyle = $_POST['bodystyle'];
$price = $_POST['price'];

// use static values in that case
$pic1= basename($_FILES['photo']['name'][0]);

$pic2= basename($_FILES['photo']['name'][1]);

if(!empty($_FILES['photo']['tmp_name']))
{
	
	// Number of uploaded files
	 $num_files = count($_FILES['photo']['tmp_name']);

	 /** loop through the array of files ***/
	 for($i=0; $i < $num_files;$i++)
	 {
		 // check if there is a file in the array
		 if(!is_uploaded_file($_FILES['photo']['tmp_name'][$i]))
		 {
			 $messages[] = 'No file uploaded';
		 }
		 else
		 {
			 // move the file to the specified dir
			 if(move_uploaded_file($_FILES['photo']['tmp_name'][$i],$target.'/'.$_FILES['photo']['name'][$i]))
			 {
			
				 $messages[] = $_FILES['photo']['name'][$i].' uploaded';
				
			 }
			 else
			 {
				 // an error message
				 $messages[] = 'Uploading '.$_FILES['photo']['name'][$i].' Failed';
			 }
		 }
	 }
	
	// prepare insert query statement
	 $sql = "INSERT INTO privatelistings (submittedby,listingtitle,make,model,exteriorcolour,enginesize,fueltype,yearregistered,transmission,mileage,nodoors,bodystyle,price,photo,photo1)
	 VALUES ('$submittedby', '$listingtitle', '$make', '$model', '$exteriorcolour', '$enginesize', '$fueltype', '$yearregistered', '$transmission', '$mileage', '$nodoors', '$bodystyle', '$price', '$pic1', '$pic2')";
	 $result = mysql_query($sql) or die ("Could not insert data into DB: " . mysql_error());
	 header("Location: private-listing-added-successfully.php?msg=Listing Added successfully");
	 exit;
	 
	 
	
	 /*echo '<pre>'.print_r($sql, true).'</pre>';
	
	 echo '<pre>'.print_r($messages, 1).'</pre>';*/

	 // execute query...
	 $result = mysql_query($sql) or die(mysql_error());
	
}

?>

Below is the view listings page

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?php 
$title = "Private Seller Listings";

include ( 'includes/header.php' );

require_once("functions.php");

$con=mysqli_connect("host","username","password","databasename");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Start a session for error reporting
session_start();

$submittedby=$_POST['submittedby'];
$listingtitle=$_POST['listingtitle'];

$query = mysqli_query($con,"SELECT listingtitle FROM privatelistings WHERE item LIKE '%$submittedby%'");

echo "<p>Title: $listingtitle</p>";

mysqli_close($con);

?>

<?php include( 'includes/footer.php' ); ?>

Does that help bit more

ianhaney50 · October 6, 2014

Sorry just amended some of the coding and now looks like the following

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?php 
$title = "Private Seller Listings";

include ( 'includes/header.php' );

require_once("functions.php");

$con=mysqli_connect("host","username","password","databasename");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Start a session for error reporting
session_start();

$submittedby = "";
$listingtitle = "";
if(isset($_POST['submittedby'])){ $submittedby = $_POST['submittedby']; }
if(isset($_POST['listingtitle'])){ $listingtitle = $_POST['litingtitle']; }

$query = mysqli_query($con,"SELECT listingtitle FROM privatelistings WHERE item LIKE '%$submittedby%'");

echo "<p>Title: $listingtitle</p>"; <---THIS LINE IS NOT WORKING AS IS ONLY DISPLAYING THE WORD TITLE AND NOT THE LISTING TITLE-->

mysqli_close($con);

?>

<?php include( 'includes/footer.php' ); ?>

Sorry is in capital letters, is only way I could think of getting it to stand out

ianhaney50 · October 6, 2014

I have changed the php coding in the view listing page as know I need to pull the user id from the privatemembers table to tie up with the listings in the privatelistings table, is that right?

so have came up with the following coding, it does not produce any errors it just don't display the title

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?php 
$title = "Private Seller Listings";

include ( 'includes/header.php' );

require_once("functions.php");

$con=mysqli_connect("host","username","password","databasename");
// Check connection
if (mysqli_connect_errno()) {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// Start a session for error reporting
session_start();

$listingtitle = "";
/*$listingtitle = "$listingtitle";
if(isset($_POST['listingtitle'])){ $listingtitle = $_POST['litingtitle']; }*/

$query = mysqli_query($con,"SELECT privatelistings.listingtitle FROM privatelistings INNER JOIN privatemembers ON listingtitle.privatelistings=privatemembers.id");

echo "<p>Title: $listingtitle</p>";

mysqli_close($con);

?>

<?php include( 'includes/footer.php' ); ?>

am going to keep plugging away and see if I can work it out for my own as is not fair keep asking for help on here

cyberRobot · October 6, 2014

Just in case it was missed, have you tried processing the query results as suggested in post #5? See the part about using mysqli_fetch_assoc():

http://forums.phpfreaks.com/topic/291471-php-where-clause/?do=findComment&comment=1492871

ianhaney50 · October 6, 2014

Hi cyberRobot

To be honest I did not see that

I did change the echo statement as I have

$listingtitle=$_POST['listingtitle'];

so now the echo line looks like

echo "<p>Title: $listingtitle</p>";

bit it gives me the following error

Notice: Undefined index: listingtitle

so am guessing best bet I need to look at the mysqli_fetch_array link just above for it to work, that right?

Psycho · October 6, 2014

I think you are making this way more difficult than it needs to be.

On the form to submit a listing you have a hidden field for submittedby

<input type="hidden" name="submittedby">

. . . BUT it has no value! Since it is hidden the user cannot enter a value either (at least not normally).

Then on the processing page you attempt to use that field when saving new records

$submittedby = $_POST['submittedby'];
// . . . 
$sql = "INSERT INTO privatelistings (submittedby,listingtitle,make,model,exteriorcolour,enginesize,fueltype,yearregistered,transmission,mileage,nodoors,bodystyle,price,photo,photo1)

     VALUES ('$submittedby', '$listingtitle', '$make', '$model', '$exteriorcolour', '$enginesize', '$fueltype', '$yearregistered', '$transmission', '$mileage', '$nodoors', '$bodystyle', '$price', '$pic1', '$pic2')";

Unless I am missing something that "submittedby" value in the inserted records will be empty. So, your code to retrieve records using a LIKE on the submittedby will not find any results. Everything is backwards.

I will assume that users must log in to use the site, therefore there would have to be some way to track that a user is logged in. I will further assume that you have the userID value in session data. Assuming this is all true, this is how you should be approaching this:

1. No input field is needed for the submittedby on the forms.

2. On the page that processes the form submissions, use the session value for the userID in the INSERT query to associate the listing with the user who submitted it.

3. On the profile page use a value on the URL to identify the user for which the page should be displayed. You don't say if users can view other users pages or not. But, I'll assume that you will at least allow an admin to view others' profile pages. If you want to restrict users from seeing other users' pages you can checked the logged in userID to the userID of the requested page. So, on the profile page, use the userID passed in the URL (i.e. $_GET value) to query the listings associated with that user.

You would only use LIKE in a query for 'search' type queries. Not where you want to see all records related to a specific key.

ianhaney50 · October 6, 2014

Ok cool think I got it now, will have a go and play and see how I get on

ianhaney50 · October 6, 2014

Thank you appreciate the help, sorry yeah my fault did forget to mention that users won't be able to view other users pages

ianhaney50 · October 6, 2014

Am getting somewhere now after the steps mentioned above

1) DONE

2) Got the session value for the userID in the INSERT query to associate the listing with the user who submitted it.

3) On the profile page, I got the link called view listings and am already getting the user id displayed in the url - view-private-listings.php?id=4 so now just need to get the listings only displayed on that view-private-listings.php page for that user ie id=4 then same for another user if they was logged in so am getting the user id passed in the url so that side is ok just the user id 4 listings displayed on the view listings page if submitted by user id 4 but at the mo am getting all the listings displayed on the view listings page submitted by user id 4 or not

will have another look tomorrow morning and see if can work it out but feel a step closer, sorry for being a pain and difficult, I know it can be done and is easy when you know how but will get there im determined with it

Thank you, sorry again

Psycho · October 6, 2014

If you are certain that you will never use this page for others to view the user's listing (even an administrator), then you don't even need to pass the ID on the query string. Just get the ID from the session data. If you DO want to have the ability for an admin to view the page of a user then go ahead and keep it in the URL and do a check to see if the user requesting the page has the same ID as the one they are requesting OR if the user is an admin.

Rough example:

if($_SESSION['user_id'] <> $_GET['user_id'] || $_SESSION['admin'] != 1)
{
    echo "You do not have rights to view the page.";
   //perform error logic and / or redirect the user to another page.
}

Sign In

php where clause

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information