PHP selecting a single value from several columns in mysql

[designsweb]

Hi Guys, I hope this is not too confusing, I am not by any means a PHP expert.

 

I have a database which houses data from a spreadsheet for our leaflet distribution company, basically the data I need to extract is simply the clients name which can be in any of 8 columns, to display in the log in area only that clients delivered areas.

 

1st the client logs in, then they are taken to a page to enter their campaign name (given to them by us which is the data we want to search) then they are taken to our "tracking" page where my problems begin, all I want to do is have the tracking page search 'client_a' through 'client_h for the value that the client entered on the previous page and display only those results, another client does the same with another data value and thus gets his company results displayed.

 

I think that I am struggling with the $_GET part at the beginning, I've tried many many ways but can only get results by individual columns. What I think I need is $client=$_GET[' ']; duplicated 8 times (client_a -client_h) and then to display the results from any of the 8 columns in the first <div> of the $dynamicList2

 

I have tried;

$client=$_GET['client_a'];

$client=$_GET['client_b']; ....etc etc.

 

$client=$_GET['client_a'] ['client_b'] ....etc etc.;

 

Logic tells me it needs to be this way because I only want to output the variable '$client ' in the <div> because wherever it got the data from is immaterial.

 

but I think I need to have say

$client_a=$_GET['client_a'];

$client_b=$_GET['client_b'];  ......etc etc

 

but then if I am correct how do I output the data into the <div> as I can't have;

<div id="client" class="dynamic"> ' . $client_a . ', ' . $client_b . ', ....etc etc </div> or can I? I've tried but it only displays every row with no clients at all

 

I have also tried without success the IN command below with each scenario, I now think that I have so many ideas going round my head that I cannot see the logical solution.

 

$sql=mysql_query("SELECT * FROM routes WHERE '$client' IN(client_a, client_b, client_c, client_d, client_e, client_f, client_g, client-h) ORDER BY date DESC");

 

The full code below is what I get success from with a single query:

<?php
include"../scripts/connect_to_mysql.php";

$client=$_GET['client_a']; 

$dynamicList2="";
$sql=mysql_query("SELECT * FROM routes WHERE client_a='$client' ORDER BY date DESC");
$routesCount=mysql_num_rows($sql);
if ($routesCount > 0 ){
	while($row=mysql_fetch_array($sql)){
		$id=$row["id"];
		$client_a=$row["client_a"];
		$area=$row["area"];
		$date=$row["date"];
		$roads=$row["roads"];		
		$missed=$row["missed"];
		$agent=$row["agent"];
		
$dynamicList2.='<div id="client" class="dynamic"> ' . $client_a . '</div>
 				<div id="area" class="dynamic"> ' . $area . '</div>
 				<div id="completed" class="dynamic"> ' . $date . '</div>
				<div id="road" class="dynamic"> ' . $roads . '</div>
				<div id="undelivered" class="dynamic"> ' . $missed . '</div>
 				<div id="agent" class="dynamic"> ' . $agent . '</div>';
	}
}else{
	$dynamicList2="Details of the campaign are yet to be uploaded, please try later.";
}
include ("../scripts/mysql_close.php");
?>

Many thanks

Ian.

[Ch0cu3r]

Your database design is  not the best, but you'll need to compare each column to $client.

$sql=mysql_query("SELECT * FROM routes WHERE client_a = '$client' OR client_b = '$client' OR client_c = '$client' OR client_d = '$client' OR client_e = '$client' OR client_f = '$client' OR client_g = '$client' OR client-h = '$client' ORDER BY date DESC");

Ideally you'll want to redesign your database so each time you add a client to a route you'd create a new row in a different table. Not list each client in individual columns per route.

 

I'd setup three tables, clients, routes and client_routes

The clients table stores all the information about that client, such as name, address, username, password, email etc

The routes table stores the information about that route, such the location, time of day etc.

 

When you add a client to a route you create a new row in the client_routes table. Here you'll record the primary key for the client and the primary key for the route they are assigned to.

 

You'd then query the client_routes table to get all the routes they are assigned to, you'd use a JOIN to get the details from the client and routes table. Eg

SELECT c.name,                                 # get the client name
       r.area, r.date, r.missed, r.agent,      # get the route area, date, missed and agent
FROM client_routes cr                          # query the client_routes table
JOIN routes r ON r.id USING(cr.route_id)       # get the route that matches the route id
JOIN clients c ON c.id USING(cr.client_id)     # get the client that matches the client id
WHERE cr.client_id = $client_id                # select the client_routes that matches the client id

Edited January 30, 2014 by Ch0cu3r

[designsweb]

Thanks Ch0cu3r,

 

I will certainly look at re-designing the database as you suggested, in the meantime I'd like to see if I can just get the query to work as the data is entered into a simple excel spreadsheet, the reason that the clients are in their own columns is so that client_a, b and c can be logged against a single road as delivered and not just to an area, so it would require entering the road 3 times.

 

At the moment the client logs into the system and then selects the button labeled client A, B, C,........etc and is then taken to the page that has been created for that client with it's own unique script.

 

I'm trying to simplify the process by letting them enter their business and then dynamically retrieve the results based on what they entered in the input box.

 

I can't get your suggestion to work I understand how you've written it, but I'm not sure what to put (a) in the form name, as it can't be client_a, client_b, cli..... etc and then how to write the $client-$_GET['???????????'];

 

I really do appreciate the help

Ian.

[Barand]

The use of IN should work except it looks as though you have a misspelling in client_h

$sql=mysql_query("SELECT * FROM routes WHERE '$client' IN(client_a, client_b, client_c, client_d, client_e, client_f, client_g, client-h) ORDER BY date DESC");
                                                                                                                                      ^
                                                                                                                                      |
                                                                                                                                 underscore?