Join 2 "select" functions need help I'm very new!

Hello! I’m very self taught and don’t know what I’m doing. I have checked a few threads on this webpage and I have ended up with the following. I have 4 select functions being ran and I want to combine their result. I will list 2 and show what amalgamation I have created with a third. All of the lines run perfectly on their own using seperate functions - however they return a list with many duplicates. I am trying to make no duplicates exist by combining the selects into 1 select.

1. this is a select line that pulls exact first name, last, name, and zip matches.
$sql = sprintf(“select * from bidder where auctioneer_id = “%s” AND zip = “$zip” AND last_name = “$last_name” AND first_name = “$first_name” ORDER BY last_name ASC, first_name ASC”, $auctioneer_id, $first_name, $zip);

2. this is a select line that pulls all first name matches.
$sql = sprintf(“select * from bidder where auctioneer_id = “%s” AND first_name = “%s” ORDER BY last_name ASC, first_name ASC”, $auctioneer_id, $first_name);

3. what my hubris has brought me to; it pulls all exact license number matches and correctly displays them but shows no other entries.
if($license_number != “”)
$sql = sprintf(“select * from bidder where auctioneer_id = “%s” AND license_number = “$license_number” UNION ALL select * from bidder where auctioneer_id = “%s” AND zip = “$zip” AND last_name = “$last_name” AND first_name = “$first_name” UNION ALL select * from bidder where auctioneer_id = “%s” AND first_name = “%s” ORDER BY last_name ASC, first_name ASC”, $auctioneer_id, $first_name, $license_number, $zip);

I think you’re overthinking it some…all you need to do is set your WHERE CLAUSE appropriately

So in all three of your statements, the auctioneer was required. Then you needed EITHER the license number OR the first name OR the zip code as well as the first and last name

So the WHERE clause is now built that way. The EITHER conditions are now contained in parenthesis (note the parenthesis around zip and first and last conditions, making them one condition). That’s why I formatted it the way I did too - the OR conditions are grouped together.

(and I didn’t change it for simplicity sake, but SELECT * is almost always a bad idea…select the individual fields you need)

SELECT * 
  FROM bidder 
 WHERE auctioneer_id = “%s” 
   AND (license_number = “$license_number” OR
        first_name = “$first_name” OR
        (zip = “$zip” AND 
         last_name = “$last_name” AND 
         first_name = “$first_name”))
  ORDER BY last_name ASC
         , first_name ASC
1 Like

I think a piece may be missing and maybe if I provide the grand picture it will make more sense. I’m doing a database search for “people” based on scanning a card number. It needs to have a procedure to it to return all people with 1. same #, 2. same Name and Zip, 3. Same Name, 4. Same First Name only.

My previous iteration was 4 separate functions doing each of those concepts. But it would return the correct person 4 times and others that matched in different positions 1-2 times.

Currently with your code implemented I am returned no matches at all. With my amalgamation I am returned only exact # matches. I can paste the call as well as the entire function if better suited.

I think you’re going to have to, because what you’re saying makes no sense. #3 is a more generic version of #2 and #4 is a more generic version of #3, so I don’t know what you’re trying to do…

Is the card number the auctioneer_id?

Negative; Auctioneer_ID is a set variable that is currently = 1 for all table entries; but as expansion happens there will be more. Large post to follow.

function get_selected_biddersSUPER($auctioneer_id, $last_name, $first_name, $license_number, $zip, $limit)
	{
		global $debug;
		global $mysql_connection;
		global $mysql_userid;
		global $mysql_password;
		global $bidder_id;
		global $HTTP_SESSION_VARS;
		global $db_name;
		global $auction_id;

		//this function is called after edit_bidder.inc populates the form
		//so you always see the first bidder in the table on the form
		//until you select a bidder from the list.

		$conn = mysql_connect($mysql_connection, $mysql_userid, $mysql_password);
		if($conn == false)
		{
		   echo "get_items: unable to connect to mysql<br>";
		}
		else
		{
		   //echo '<option value="NONE">Select bidder</option>';

		   if(mysql_select_db($db_name, $conn))
		   {
		
				if($license_number != "")
					$sql = sprintf("SELECT * 
									FROM bidder 
									WHERE auctioneer_id = \“%s\” 
									AND (license_number = \“$license_number\” OR
										zip = \“$zip\” AND 
										last_name = \“$last_name\” AND 
										first_name = \“$first_name\”) OR first_name = \“$first_name”\)
									ORDER BY last_name ASC, first_name ASC", $auctioneer_id, $first_name, $license_number, $zip);
	
				//LFZ	$sql = sprintf("select * from bidder where auctioneer_id = \"%s\" AND zip = \"$zip\" AND last_name = \"$last_name\" AND first_name = \"$first_name\"  ORDER BY last_name ASC, first_name ASC", $auctioneer_id, $first_name, $zip);
				
				//F $sql = sprintf("select * from bidder where auctioneer_id = \"%s\" AND first_name = \"%s\"  ORDER BY last_name ASC, first_name ASC", $auctioneer_id, $first_name);
				
				if($result = mysql_query($sql))
				{
					$num_rows = mysql_num_rows($result);
					$counter = 0;

				   	while ($counter < $num_rows)
				   	{
						if($next_row = mysql_fetch_array($result))
						{
							$temp_bidder_id = $next_row['id'];
							if(($bidder_id == null) || ($bidder_id == ""))
							{
								$bidder_id = $temp_bidder_id;
								$HTTP_SESSION_VARS['bidder_id'] = $bidder_id;
							}
							$last_name = $next_row['last_name'];
							$first_name = $next_row['first_name'];
							$mi_name = $next_row['mi_name'];
							$city = $next_row['city'];
							$temp_bidder_found = $next_row['id'];

							$temp_name = 'SU';
							$temp_name .= ' ';
							$temp_name .= $last_name;
							$temp_name .= ', ';
							$temp_name .= $first_name;
							$temp_name .= ' ';
							$temp_name .= $mi_name;

							
							if($city)
							{
								$temp_name .= ' - ';
								$temp_name .= $city;
							}

							if($temp_bidder_id == $bidder_id)
							{
								echo '<option value='; echo $temp_bidder_id; echo ' selected>'; echo $temp_name; echo '</option>';
								$temp_bidder_number = $next_row['permanent_bidder_number'];
							}
							else
							{
								echo '<option value='; echo $temp_bidder_id; echo '>'; echo $temp_name; echo '</option>';
							}

						}	//end if next_row=
						else
						{
							break;
						}
						$counter++;
					}
				}

		  }
		  mysql_close($conn);
		}	//end else $conn == true

		if(($temp_bidder_number == "") || ($temp_bidder_number == 0))
			$temp_bidder_number = get_next_bidder_number($auction_id);

		return $temp_bidder_number;

}	//end get_bidders_by_auctioneer()

get_selected_biddersSUPER($auctioneer_id, $select_lname, $select_fname, $license_number, $zip, 10);

This is the call on a seperate .php

I added the \ marks to your command in the main function as a test to see if it would fix anything. I know not what it does, so I kind of stab in the dark until it works.

In all actuality it should work like this in my brain but I don’t know how to code it.

IF license match found
THEN return record.

IF license match not found
THEN return records matching Last Name, First Name, Zip
AND return records matching Last Name, First Name
AND return records matching First Name.
OMIT duplicates.

I got it to work KIND OF. My search now operates multiple functions BUT it sorts by name EVEN IF it finds an exact license number match

$sql = sprintf(“select *
FROM bidder
WHERE auctioneer_id = “$auctioneer_id” AND license_number = “$license_number”
UNION select *
FROM bidder
WHERE auctioneer_id = “$auctioneer_id” AND first_name = “$first_name”
ORDER BY last_name ASC, first_name ASC”, $auctioneer_id, $first_name, $license_number);


my return comes back with all of the names I expect them to. BUT it sorts by last and first name. I cannot sort directly by $license_number otherwise it would just take my string of licenses and alphabetize them which is NOT good.

I need a solution for when it finds a record with matching ID# it forces it to be first in the list. For example I will create some variables for use. L# = license number match; LFZ = last name, first name, zip match; LF = last name first name match; F = first name match.

Previous iteration I was returned
L#
LFZ
LFZ
LFZ
LF
LF
LF
LF
LF
F
F
F
F
F
F
F

Now I am returned (I DO NOT HAVE LFZ OR LF FUNCTION RUNNING SO THEY ARE OMITED)

F
F
F
F
F
F
L#
F
F
F
F

Even though it looks like it isn’t, this way is MUCH better in terms of quantity. Because the first iteration returned lets say 100 rows with 4 being duplicates of the intended find and 20 being duplicates of the nonintended finds.

Now it doesn’t return duplicates, but they need to be ordered correctly.

select as rank comes to mind but I don’t know how to write it

11 posts were split to a new topic: Obsolete mysql

Now you’re changing criteria, and those changes are MAJOR. Now you’re ranking based on those four different sorts, a major component that would have been nice to know ahead of time. I’m not sure how to tackle that…let me think about that.

Instead of posting comments like this which add no value to the task at hand, why not post the ONE LINE CORRECTION that would be required to resolve that particular issue, especially since you have no idea if he does any safety checks outside the function or now. Or I don’t know, help solve the poor guy/girl’s problem before taking the thread off task?

1 Like

Very sorry about that - I’m very very very new to coding as a whole and even newer to this website or posting on forums myself. Usually I’m just reading posts to find answers, ha!

Well - I didn’t know I have this problem and I guess I may care in the future for sure. At the moment I need a working program so it can begin production - like DaveMaxwell said. I’m SURE beyond reason that the coding has major issues but until I have a product to work with there’s not much to be done. I will of course look into this PDO and updating PHP - if it isn’t going to break a few thousand pages then I don’t mind pushing that button now. But of course this is going to take a full round of me backing up all of my data prior to an update to make sure. Thank you for the concerns. For the time being I will be trying this ranking structure and post my results as I get to them.

Again, not really because we have NO IDEA what querystring/form data sanitization is being done OUTSIDE THE FUNCTION. PDO is the easiest way, and arguably the most secure way, but it is NOT THE ONLY WAY…

My issue is the approach. Just throwing doom and gloom type statement while not showing just how easy it is to transition. MOST people, when looking down the gullet of a eminent timeline will not stop what they’re doing to go somewhere else to read up on best practices.

Why couldn’t something like this be done? Help while showing better ways to accomplish it…


@agerads, while I don’t approve of the tone taken, @benanamen and @chorn are correct in that the mysql approach is severely out of date and potentially dangerous. It has been phased out of the current versions of PHP - my sources say it was last supported in PHP5 and if your server is still running PHP5, you should consider petitioning your host upgrades your versions of PHP, or look to change servers.

At the very least, upgrade to use mysqli instead. But @benanamen is definitely correct in that PDO is considered best practice for any database interaction. It has the latest SQL injection protections and you should consider moving to it ASAP.

Using the excellent tutorial that @benanamen linked to, this should essentially do what you need using PDO.

Obviously, I don’t have access to your database, but I used PHPFiddle to run essentially a similar query using their baseline table via PDO, so the concept should be sound. I would suggest running the query directly in your mySQL admin area to work out any errors in the query…

		$conn = new PDO($mysql_connection, $mysql_userid, $mysql_password);

		$sql = "SELECT id
					 , last_name
					 , first_name
					 , mi_name
					 , city
					 , zip
					 , license_number
				  FROM bidder 
				 WHERE auctioneer_id = :auctioneer_id 
				   AND (license_number = :license_number OR
						(zip = :zip AND last_name = :last_name AND first_name = :first_name) OR
						(last_name = :last_name AND first_name = :first_name) OR
						first_name = :first_name)
				  ORDER BY CASE WHEN license_number = :license_number THEN 5
								WHEN zip = :zip AND last_name = :last_name AND first_name = :first_name THEN 4
								WHEN last_name = :last_name AND first_name = :first_name THEN 3
								WHEN first_name = :first_name THEN 2 END DESC
						 , last_name ASC
						 , first_name ASC";
		
		$statement = $conn->prepare($sql);
		$retults = $statement->execute(['auctioneer_id' => $auctioneer_id, 'license_number' => $license_number, 'zip' => $zip, 'last_name' => $last_name, 'first_name' => $first_name]);
		while ($next_row = $results->fetch()) {
			$temp_bidder_id = $next_row['id'];
			if(($bidder_id == null) || ($bidder_id == ""))
			{
				$bidder_id = $temp_bidder_id;
				$HTTP_SESSION_VARS['bidder_id'] = $bidder_id;
			}
			$last_name = $next_row['last_name'];
			$first_name = $next_row['first_name'];
			$mi_name = $next_row['mi_name'];
			$city = $next_row['city'];
			$temp_bidder_found = $next_row['id'];

			$temp_name = 'SU';
			$temp_name .= ' ';
			$temp_name .= $last_name;
			$temp_name .= ', ';
			$temp_name .= $first_name;
			$temp_name .= ' ';
			$temp_name .= $mi_name;

			
			if($city)
			{
				$temp_name .= ' - ';
				$temp_name .= $city;
			}

			if($temp_bidder_id == $bidder_id)
			{
				echo '<option value='; echo $temp_bidder_id; echo ' selected>'; echo $temp_name; echo '</option>';
				$temp_bidder_number = $next_row['permanent_bidder_number'];
			}
			else
			{
				echo '<option value='; echo $temp_bidder_id; echo '>'; echo $temp_name; echo '</option>';
			}			
		}

For those involved, all of the off-topic discussion was trashing the topic and as best as possible has been moved
Obsolete mysql

3 Likes

@DaveMaxwell I appreciate your help. I’ll give all of this a look and be back with my questions I’m sure :slight_smile:

@DaveMaxwell Well - I found a pseudo solution I’m posting in case someone 2 years from now needs it and they too stubbornly don’t want to update quite yet. It all came down to where I placed my order statement. purely you have to sort each union separately and then it appears as I wish it to. While also omitting duplicates.

select *
FROM bidder
WHERE auctioneer_id = “$auctioneer_id” AND license_number = “$license_number” ORDER BY last_name ASC, first_name ASC)
UNION (select *
FROM bidder
WHERE auctioneer_id = “$auctioneer_id” AND first_name = “$first_name” AND last_name = “$last_name” ORDER BY last_name ASC, first_name ASC)
UNION (select *
FROM bidder
WHERE auctioneer_id = “$auctioneer_id” AND first_name = “$first_name” ORDER BY last_name ASC, first_name ASC)

I’ve bookmarked all of this so when I get everything else and the sun and moon aligned I can follow the PDO guide and get cracking into where I should be. I quickly skimmed the material and it seems quite manageable. Thank you much.