MySQL search using PHP problem

[fja3omega]

This is my code in PHP for searching two tables.

It works but then an error occurs whenever $calls ="SELECT * FROM  `table2` WHERE  `Company` LIKE '$company'"; gets a variable data with an (') inside.  Like Cow's, It's, Isn't or Don't.  I've been trying the search in MySQL phpMyAdmin and it shows that with ' you need %'% to get it to show.  How do i put %$var% in my $calls variable?

 

Heres my code:

 

<?php

$count = 0;

$con = mysql_connect("localhost","username","password");

if (!$con)

  {

  die('Could not connect: ' . mysql_error());

  }

mysql_select_db("Database", $con);

$result = mysql_query("SELECT * FROM table1");

while($row = mysql_fetch_array($result))

{

$count++;

$company = $row['Company'];

$calls ="SELECT * FROM  `table2` WHERE  `Company` LIKE '$company'";

$result2 = mysql_query($calls);

while($row2 = mysql_fetch_array($result2))

{

$email = $row2['Email Address'];

$cntper = $row2['Contact Person'];

}

echo "<br />--------------------------------------------------------------------------------------<br />Company: ".$company."<br />Email Address: ".$email."<br />Contact Person: ".$cntper."<br />Count: ".$count;

ob_flush();

flush();

}

mysql_close($con);

?>

 

Please and Thank You...

[ignace]

$calls ="SELECT * FROM  `table2` WHERE  `Company` LIKE '%$company%'";

[fja3omega]

$calls ="SELECT * FROM  `table2` WHERE  `Company` LIKE '%$company%'";

 

that didn't work.

I tried that already.

hmmm... do i need to parse the variable? pregmatchall?

i don't even know how to start with this...

[ignace]

that didn't work.

 

How do you mean that didn't work, did you get an error? Maybe you could start to define the actual problem instead of just saying "it didn't work" and keep us guessing in the dark.

[fja3omega]

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

Company: AQUALINE INTERNATIONAL, INC.

Email Address: [email protected]

Contact Person: Ana Rodriguez

Count: 22

 

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

Company: AQUALINE PRODUCTS INC.

Email Address: [email protected]

Contact Person: Tom Lukk

Count: 23

 

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

Company: ARK FLOORS INC.

Email Address: [email protected]

Contact Person: Erica Shu

Count: 24

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /mailme.php on line 16

 

The problem is whats inside here

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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

Company: Art's Trading Co

Email Address: [email protected]

Contact Person: Erica Shu

Count: 25

 

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

its got the same email and contact person as the one listed above it.  its got an ' inside Art's so that may be the problem.

 

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

Company: Asia Direct Imports

Email Address: [email protected]

Contact Person: Richard Moore

Count: 26

[ignace]

Here I re-wrote your script (this shows everything from table1 and table2), try it:

 

$db = mysql_connect('localhost', 'username', 'password');
if (FALSE === $db) {
  header('HTTP/1.0 500 Internal Error');
  trigger_error('Failed to connect to the database server, verify your details. (mysql said: ' . mysql_error() . ')');
  
  echo 'The server is experiencing some temporary problems and will be resolved soon, please check back soon.';
  exit(0);
}

if (FALSE === mysql_select_db('database', $db)) {
  header('HTTP/1.0 500 Internal Error');
  trigger_error('Failed to select the database, verify if the database name exists.');
  
  echo 'The server is experiencing some temporary problems and will be resolved soon, please check back soon.';
  exit(0);
}

$number = 1;
$separator = '-- %03s ' . str_repeat('-', 60);

$result = mysql_query('SELECT * FROM table1 JOIN table2 USING Company');
while ($row = mysql_fetch_assoc($result)) {
  $company = $row['Company'];
  $email = $row['Email Address'];
  $contactPerson = $row['Contact Person'];
  
  // -- 001 -----------------------------------------------
  // Company: 
  // Email Address: 
  // Contact Person: 
  echo sprintf($separator, $number++),
       'Company: ', $company, "<br/>\n",
       'Email Address: ', $email, "<br/>\n",
       'Contact Person: ', $contactPerson, "<br/>\n<br/>\n",
}

[Maq]

The error is produced because you have to escape the input - mysql_real_escape_string.