Query database?

[andy_b_1502]

Is it possible to query my database and display results from a varible that changes each time from a users search?

 

I know how to get results from a variable like a field name and display them but i was wondering if for example; the user searches for criteria and gets the result there looking for (postcode); they then click that result in the browser and are redirected to another page with lots more information.(company_name and location).

 

There result from the original search would be different each time but i would like to know if i could take that result and display all other fields in my table associated with that one result.

 

***Freelistings***

id company_name location      postcode

 

1 Dave's Storage  London        PO Box1

 

2 Steve's Trucks  Birmingham WS12 3

 

3 Sue's Tyres      Manchester  M14 6

 

4 Paul's              Birmingham WS11 7

 

***Basicpackge***

id company_name location      postcode

 

1 Storage Ltd      London      LN12 5TW

 

2 Fran's Grit        Birmingham WS5 37

 

3 Raj Walls          Manchester  M14 60

 

4 Paul's Light's    Birmingham WS12 17

 

***Premiumuser***

id company_name location      postcode

 

1 Name1              Location1    PC1

 

2 Name2              Location2    PC2

 

3 Name3              Location3    PC3

 

4 Name4              Location4    PC4

 

I'm sure this can be done, i'm just getting stuck on how to treat the user's search result. Currently it's called '%$var%'.

 

Can i use:

 

$query = "(SELECT company_name, location FROM freelistings

            WHERE company_name, location like '%$var%')       

UNION         

              (SELECT company_name, location FROM basicpackage             

  WHERE company_name, location like '%$var%')         

UNION         

  (SELECT company_name, location FROM premiumuser             

  WHERE company_name, location  like '%$var%')       

  ORDER BY postcode"; 

 

So that would be looking at all three tables, getting the details from each field that is associated with that (result) postcode. I hope i have given enough information to explain my problem i'm having. for me quite a tough one? any help would be appreciated, thanks guys in advance!

 

[mikesta707]

I'm curious as to why you have 3 different tables to hold identical information, rather than have a flag that will determine whether the are free listings, premium or whatever, but thats beyond the point.

 

When you want to use the like operator to compare multiple fields to something, you want to use AND or OR (in your case probably OR). so

SELECT company_name, location FROM freelistings

            WHERE company_name, location like '%$var%'

 

should be

SELECT company_name, location FROM freelistings

            WHERE company_name like '%var%' OR location like '%$var%')

 

of course you will need a similar change for the other sub queries. Also, when using order by, you need to specifcy in which direction (ASC or DESC)

[andy_b_1502]

Thank you for that mikesta707.

 

I have three tables for paying memebers and non-paying memebers. They do have different fields in each but for the example i just want the company_name and location of each to be displayed.

 

So let me fully get my head around this one, that code will take the result which is '%$var%' and will look at my tables; if that '%$var%' is in them it will display in the browser the company_name and location next to it?

 

I will go off and research ASC or DESC right now, thanks again for the help

[andy_b_1502]

I was wondering if i needed to assign ASC as it states that it is the default if none is defined, i've assigned ASC anyway as a precaution!

 

The script is searching the tables but bringing back zero results even though i know 100% that what i searched for is in one table. (although no  error messages are displayed as a bonus!) strange?

 

 

 

[andy_b_1502]

Here's all my tables:

 

***freelistings***                                                                             

id  company_name  location  postcode   

1    Davo's Storage  Cannock  WS122SH 

2

 

***basicpackage***

id  compnay_name  description  location postcode

1  Test Storage      Test            Test      WS12 5SH

2

 

 

***premiumuser***     

id  company_name  company_description  location  postcode

1

2

 

So as you can see, if the user searches WS12 2SH. I want it to display all the other info in the row too? Thanks

[andy_b_1502]

Should it be something like this:

 

$query = "(SELECT postcode, company_name FROM freelistings
             WHERE postcode like '%$trimmed%')         
		UNION           
	  (SELECT postcode, company_name FROM basicpackage              
	  	 WHERE postcode like '%$trimmed%')          
		UNION           
	  (SELECT postcode, company_name FROM premiumuser              
	  	 WHERE postcode like '%$trimmed%')         
	  ORDER BY postcode"; // EDIT HERE and specify your table and field names for the SQL query

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
$result = mysql_query($query) or die("<b>Query:</b> $query<br><b>Error</b>: " . mysql_error());



// If we have no results, offer a google search as an alternative

if ($numrows == 0)
  {
  echo "<h4>Results</h4>";
  echo "<p>Sorry, your search: "" . $trimmed . "" returned zero results</p>";


// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");


// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>";

// begin to show results set
echo "<a href=\"localarea.php?var=$var\">Results for $q;</a>";
$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  $title = $row['postcode'];
  $name = $row['company_name'];

  echo " $title" ;
  echo " $company_name" ;
  $count++ ;
  }

 

the WHERE should be; if it matches the postcode, then show the company_name on so on...?  :confused:

[Drummin]

I'm curious as to why you have 3 different tables to hold identical information, rather than have a flag that will determine whether the are free listings, premium or whatever, but thats beyond the point.

I agree.  Have a single table and have a field for setting user type.

[andy_b_1502]

Thanks Drummin, i feel i need to have the three; one for users to add details of their companies for free, one for users who have paid a monthly basic-fee (a little more info) and finally one for users who have paid for a premium package (the most info) etc.

 

So i need the script to check all three tables in tandem?

[Drummin]

So what is not working with what you've tried?  Did you try mikesta707 suggestion of changing the WHERE fields?  Are the users filling out a search form that has this search criteria  defined as in Postcode etc?  If so, then the table field names called and WHERE conditions can be changed depending on the search.

[Drummin]

Assuming you are getting a search field as well as a variable for $_POST values, wouldn't something like this work?

<?php
$field=$_POST['field'];
$var=$_POST['var'];
$check = mysql_query("SELECT id,company_name,location,postcode FROM freelistings,basicpackage,premiumuser WHERE $field like '%$var%' ORDER BY postcode ASC" or die(mysql_error());
WHILE ($resultchk = mysql_fetch_array($check)){
$id=$resultchk['id'];	
$company_name=$resultchk['company_name'];	
$location=$resultchk['location'];	
$postcode=$resultchk['postcode'];	
}
?>

[awjudd]

@Drummin - Oh God no!  CROSS JOINing each of the tables for this is a bad idea.  As well, you will get an ambiguous column error if you do that because $field is in all of them (not to mention your code is very susceptible to SQL injection).

 

// Validate the field type
$availableFields = array ( 'postcode', 'location', 'company_name' );
if ( ! ( in_array ( $_POST [ 'field' ], $availableFields ) )
{
    die ( 'Invalid Field Selected.' );
}

$field = $_POST [ 'field' ];
$var = mysql_real_escape_string ( $_POST [ 'var' ] );

$query = 'SELECT 'freeuser' AS usertype, id, company_name, location, postcode FROM freelistings WHERE ' . $field . ' LIKE \'%' . $var . '%\'
                UNION ALL
                SELECT 'basicuser' AS usertype, id, company_name, location, postcode FROM basicpackage WHERE ' . $field . ' LIKE \'%' . $var . '%\'
                UNION ALL
                SELECT 'premiumuser' AS usertype, id, company_name, location, postcode FROM premiumuser WHERE ' . $field . ' LIKE \'%' . $var . '%\'';

 

That said, I have to agree with everyone else who suggests against using multiple tables to store this information.  It just plain old doesn't make sense.

 

~judda

[Drummin]

juddster

(not to mention your code is very susceptible to SQL injection).

I would never directly use a post variable to query DB.

And I do see your point about ambiguous column errors but I wouldn't be using three tables in the first place.

[andy_b_1502]

Thank you for input guys!

 

I'm coming round to the idea of having one table for the search if this will simplify everything.

[andy_b_1502]

Okay so ive chnaged my tables to merge them all into one big one.

 

It searches that one fine and brings the results back, now i just need to display the rest of the table content thats next to the postcode.

 

New table called "Companies"

 

id  company_name    location    postcode    basicpackage_description        premiumuser_description                              upload

1  Davo's Storage    Cannock  WS122SH   

2  test storage          Cannock  WS12 5SH    This is test basic package info

3  Steve's Transport  Cannock  WS12 10SH                                              This is test premium user information full page  uploads

4

5

 

 

Do i use the fetch array to display; company_name, location, basicpackage_description, premiumuser_description and uploads? to look something like this in the browser:

 

------------------------------------------------------------------------------------------------

"Local Area Search Results

 

Your Local Area has the below results for: ws122sh

 

You searched for: "ws122sh"

 

Company Name    Location    Description              Pictures/Video Uploads

Davo's Storage    Cannock   

Test Storage        Cannock  Company Description  Uploaded Content

Steve's Transport  Cannock  Company Description  Uploaded Content

 

-------------------------------------------------------------------------------------------------

[andy_b_1502]

This is the code isnt it?

 

$query = "(SELECT company_name, location, postcode, basicpackage_description, premiumuser_description, upload  FROM Companies
             WHERE company_name, location, postcode, basicpackage_description, premiumuser_description, upload like '%$var%')                  
	  ORDER BY company_name ASC"; 

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);
$result = mysql_query($query) or die("<b>Query:</b> $query<br><b>Error</b>: " . mysql_error());



// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }

// get results
  $query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");


// display what the person searched for
echo "<p>You searched for: "" . $var . ""</p>"; ?>

 

Currently it looks like this without any other infomration shown:

 

Your Local Area has the below results for: ws122sh

 

You searched for: "ws122sh"