Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

query search problems php

Posted on 2011-03-20
7
255 Views
Last Modified: 2012-05-11
hi all.

i have a working search box on my site that returns usernames and email from my members table like the search term.


i am now trying to make it pull in pictures that relate to the results.

please could you help me with my query:

this is my current working query:

$query="SELECT * FROM members WHERE UserName OR UserEmail LIKE '%".$Search."%'"; 
	$result=mysql_query($query) or die ("bad query '$query' : ".mysql_error());
	if (mysql_num_rows($result)>0) {
		  while ($res=mysql_fetch_array($result)) {
		  
			
			echo '<a href="users.php?id=' . $res['MemberID']. '">' . $res['UserName'] . '</a><br>';
			echo $res['UserEmail'].'<br><br>'; 
			
		  }
	}else{
		echo 'No results';
	}	
}

Open in new window


the query i have made (that is working but not showing correctly) :
$query="SELECT  members.MemberID, members.UserName, members.UserEmail, images.imgSmall FROM members, images WHERE images.memberId = members.MemberID AND members.UserName OR members.UserEmail LIKE '%".$Search."%'"; 
	$result=mysql_query($query) or die ("bad query '$query' : ".mysql_error());
	if (mysql_num_rows($result)>0) {
		  while ($res=mysql_fetch_array($result)) {
		  
		 
			echo '<img src='. $res['imgSmall'].'><br>';
			echo '<a href="users.php?id=' . $res['MemberID']. '">' . $res['UserName'] . '</a><br>';
			echo $res['UserEmail'].'<br><br>'; 
			
		  }

Open in new window


here is a screen shot of what the output of the search looks like:



i have 5 members it is just repeating the search 5 times over and not displaying the correct images for the members.
sreasa.png
0
Comment
Question by:helpchrisplz
7 Comments
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 35177296
Have you used phpMyAdmin to look at the data base tables and verify that they contain the correct data?  Also you might want to put var_dump() into the while control structure to see the contents of each row that is returned.  Something like this:

if (mysql_num_rows($result)>0)
{
    while ($res=mysql_fetch_array($result))
    {
        var_dump($row);
    }
}
0
 
LVL 1

Author Comment

by:helpchrisplz
ID: 35177514
my problem is am trying to do a WHERE and then go into a LIKE and i am not sure how to do that.

I just want it to retrieve MemberID, UserName and UserEmail from members and retrieve imgSmall from images where imgSmall  has the same MemberID as the MemberID found in table members. and then use LIKE to only show results relevant to the search

so the user will enter a username or a email into the search box then it will show users with that username with there picture and email





0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 35177779
But is the data correct?  Will your WHERE clause find the information?  That is why I want to ask you to verify the contents of the tables.
0
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:helpchrisplz
ID: 35177788
CREATE TABLE IF NOT EXISTS `images` (
  `pictureId` int(4) NOT NULL auto_increment,
  `memberId` int(11) default NULL,
  `imgLarge` varchar(255) default NULL,
  `imgSmall` varchar(255) default NULL,
  `banner` varchar(255) default NULL,
  `bannerSmall` varchar(255) default NULL,
  PRIMARY KEY  (`pictureId`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `images`
--

INSERT INTO `images` (`pictureId`, `memberId`, `imgLarge`, `imgSmall`, `banner`, `bannerSmall`) VALUES
(1, 1, 'images/71745-1920x1080-RED-SKULL.jpg', 'images/sml_71745-1920x1080-RED-SKULL.jpg', 'images/164088-1920x1080-Fractalius-is-AWESOME.jpg', 'images/sml_164088-1920x1080-Fractalius-is-AWESOME.jpg'),
(2, 2, 'images/164088-1920x1080-Fractalius-is-AWESOME.jpg', 'images/sml_164088-1920x1080-Fractalius-is-AWESOME.jpg', 'images/114288-1920x1080-Your_Not_Alone.jpg', 'images/sml_114288-1920x1080-Your_Not_Alone.jpg'),
(3, 3, '0', '0', NULL, NULL),
(4, 4, 'images/177017-1920x1080-Blue_Dragon_ctr9.JPG', 'images/sml_177017-1920x1080-Blue_Dragon_ctr9.JPG', 'images/GreenWave.jpg', 'images/sml_GreenWave.jpg'),
(5, 5, 'images/Desert.jpg', 'images/sml_Desert.jpg', 'images/Penguins.jpg', 'images/sml_Penguins.jpg');






CREATE TABLE IF NOT EXISTS `members` (
  `MemberID` int(11) NOT NULL auto_increment,
  `UserName` varchar(50) NOT NULL,
  `Password` varchar(55) NOT NULL,
  `UserEmail` varchar(55) NOT NULL,
  `Age` int(11) NOT NULL,
  `Location` varchar(55) NOT NULL,
  `Gender` varchar(55) NOT NULL,
  `Language` varchar(55) NOT NULL,
  PRIMARY KEY  (`MemberID`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `members`
--

INSERT INTO `members` (`MemberID`, `UserName`, `Password`, `UserEmail`, `Age`, `Location`, `Gender`, `Language`) VALUES
(1, 'admin', 'admin', 'admin@admin.com', 20, 'germany', 'male', 'french'),
(2, 'chris', 'chris', 'chris@chris.com', 22, 'Yarm', 'male', 'english'),
(3, 'christopher', 'christopher', 'christopher@christopher.com', 32, 'uk', 'male', 'french'),
(4, 'ben', 'ben', 'ben@ben.com', 32, 'uk', 'male', 'french'),
(5, 'ddaviesd', 'budapest', 'domdavies@hotmail.co.uk', 21, 'Middlesbrough', 'male', 'english');
0
 
LVL 1

Accepted Solution

by:
shefen earned 500 total points
ID: 35177851
You need a JOIN

SELECT  members.MemberID, members.UserName, members.UserEmail, images.imgSmall 
FROM members JOIN images ON images.memberId = members.MemberID 
WHERE members.UserName OR members.UserEmail LIKE '%".$Search."%'

Open in new window

0
 
LVL 4

Expert Comment

by:sgvill
ID: 35177869
Will something like

WHERE members.UserName OR members.UserEmail LIKE '%".$Search."%' 

Open in new window


work in your DB?  or do you have to do it like this:

WHERE members.UserName LIKE  '%".$Search."%'  OR members.UserEmail LIKE '%".$Search."%' 

Open in new window


(and a similar question in your other select statement)
0
 
LVL 1

Author Closing Comment

by:helpchrisplz
ID: 35178210
yes that is doing the trick thx
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
This article will explain how to display the first page of your Microsoft Word documents (e.g. .doc, .docx, etc...) as images in a web page programatically. I have scoured the web on a way to do this unsuccessfully. The goal is to produce something …
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question