read lines and select from mysql [Archive]

View Full Version : read lines and select from mysql

raulgonzalez

12-18-2007, 09:51 AM

Hello,

I have a list of names in a text file like:

Name 1
Name 2
Name 3

then I have a mysql database with lots of names and matching information.

I wanted to retrieve records from that database where the name = Name 1 and Name 2 and Name 3 and only those not the rest of them.

I tried using a script that reads the file line by line and run a (for loop) to go selecting 1 by one but it doesn't work.

am I heading the right direction or is there a better way to do this? Below is what I have.:

Please help.

<table align=center border=1 bgcolor=#000000 cellpadding=1 cellspacing=1>
<tr><td align=center colspan=7 bgcolor="<? echo $th_color;?>">Laredo Community College Phonebook</td></tr>
<tr>
<?
echo "<th bgcolor=$th_color2>#</th>";
echo "<th bgcolor=$th_color2>Name</th>";
echo "<th bgcolor=$th_color2>Box #</th>";
?>
</tr>

<?
require_once('include/connection.php');
?>

<?php
$lines = file('employees.txt');
$lines[] = file('employees.txt');
foreach ($lines as $line_num => $line) {
}
$i = 0;
while ($i < $line_num){

if ($line[$i] == ""){
echo "";
}
else
{
$linea = htmlspecialchars($line[$i]);
//echo $linea . " "; // to make sure I am getting the data from the text file, it works
$i++;
?>

<?
$query = "SELECT * FROM employees WHERE CONCAT(employee_name, employee_last) = '$linea' ORDER BY employee_name ASC";
if ($r = mysql_query ($query)) { // Run the query.
$n = 0;
while ($row = mysql_fetch_array ($r)) {
$n++;
$id = $row['id'];
$employee_name = ucfirst($row['employee_name']);
$employee_last = ucfirst($row['employee_last']);
$employee_box = $row['employee_box'];
$employee_full = $employee_name . $employee_last;

if (($i % 2) == 0){
$td_color = $th_color;
}
else
{
$td_color = "#ffffff";
}
echo "<tr>";
echo "<td bgcolor=\"$td_color\">" . $n . " </td>\n";

echo "<td bgcolor=\"$td_color\">" . $employee_name . " " . $employee_last . "";
//echo "<td bgcolor=\"$td_color\">" . htmlspecialchars($line[$i]) . " " . $employee_last . "";
echo "<td bgcolor=\"$td_color\">" . $employee_box . " </td>\n";
echo "</tr>";

}

}
?>

<?
//echo htmlspecialchars($line[$i]) . " ";
}
}// if statement
?>

<?
mysql_close(); // CLOSE THE FILES.
?>
</table>

awatson

12-18-2007, 11:45 AM

What does $linea end up looking like? Does it make for a correct SQL query? It seems like you'd want the where clause to look something like you'd need separate parts of the where clause for eahc name i.e. "where CONCAT(employee_name, employee_last) = name1 or CONCAT(employee_name, employee_last) = name2 OR ..."

raulgonzalez

12-18-2007, 12:17 PM

$line looks like "first_name1 last_name1"
and $employee_name looks like "first_name1"
and $employee_last looks like "last_name1"

I tried doing the concatnation. I run the query setting up an if statement just to see what the difference looks like and I get to the browser:

first_name1 last_name1 NOT EQUAL TO first_name1 last_name1

When in fact they are identical.

here is a link to the test I am running

http://www.laredo.edu/directory/view_all_box.php

Below is what I did to the code: I know is a mess:

<?
require_once('variables.php');
?>

<?
$division = "arts and sciences";
$th_color = "#EAC117";
$th_color2 = "#4E9258";
$font_face = "verdana";
$font_size = "1";
?>

<?
echo "";
echo "<img src=\"images/bar.jpg\">";
echo "";
?>
<table align=center border=1 bgcolor=#000000 cellpadding=1 cellspacing=1>
<tr><td align=center colspan=7 bgcolor="<? echo $th_color;?>">Laredo Community College Phonebook</td></tr>
<tr>
<?
echo "<th bgcolor=$th_color2>#</th>";
echo "<th bgcolor=$th_color2>Name</th>";
echo "<th bgcolor=$th_color2>Box #</th>";
?>
</tr>

<?
require_once('include/connection.php');

?>

<?php
$lines = file('employees.txt');
$lines[] = file('employees.txt');
foreach ($lines as $line_num => $line) {
}
$i = 0;
while ($i < $line_num){

if ($line[$i] == ""){
echo "";
}
else
{
$linea = htmlspecialchars($line[$i]);
echo "\$linea = " . $linea . " ";
$i++;
?>

<?
$query = "SELECT * FROM employees WHERE CONCAT(employee_name, employee_last) SOUNDS LIKE '%{$linea}%' ORDER BY employee_name ASC";
//$query = "SELECT * FROM employees ORDER BY employee_name ASC";
if ($r = mysql_query ($query)) { // Run the query.

if ($n == ""){
$n = 0;
}
while ($row = mysql_fetch_array ($r)) {
$n++;
$id = $row['id'];
$employee_name = ucfirst($row['employee_name']);
$employee_last = ucfirst($row['employee_last']);
$employee_box = $row['employee_box'];
$employee_full = $employee_name . " " . $employee_last;

if (($i % 2) == 0){
//$td_color = $th_color;
$td_color = "#ffffff";
}
else
{
$td_color = "#ffffff";
}

if ($employee_full == $linea){
echo "<tr>";
echo "<td bgcolor=\"$td_color\">" . $n . " </td>\n";

echo "<td bgcolor=\"$td_color\">" . $employee_name . " " . $employee_last . " ";
//echo "<td bgcolor=\"$td_color\">" . htmlspecialchars($line[$i]) . " " . $employee_last . " ";
echo "<td bgcolor=\"$td_color\">" . $employee_box . " </td>\n";
echo "</tr>";

}
else
{

echo "<tr>";
echo "<td bgcolor=\"$td_color\">" . $n . " </td>\n";

echo "<td bgcolor=\"$td_color\">" . $linea . " Does Not Match With " . $employee_name . " " . $employee_last . " ";
//echo "<td bgcolor=\"$td_color\">" . htmlspecialchars($line[$i]) . " " . $employee_last . " ";
echo "<td bgcolor=\"$td_color\">" . $employee_box . " </td>\n";
echo "</tr>";
//echo $i . "--" . $linea . " Does Not Match With " . $employee_name . " " . $employee_last . " ";
}

}

}
?>

<?
//echo htmlspecialchars($line[$i]) . " ";
}
}// if statement
?>

<?
mysql_close(); // CLOSE THE FILES.
?>
</table>

raulgonzalez

12-18-2007, 02:06 PM

Thanx, I figured a way to work around this thing as I run out of time. I am posting the final code just as good practice but I DO NOT recommend anybody using it. Use something cleaner if you have the time.

<?
require_once('variables.php');
?>
<?
$division = "arts and sciences";
$th_color = "#EAC117";
$th_color2 = "#4E9258";
$font_face = "verdana";
$font_size = "1";
?>

<table align=center border=1 bgcolor=#000000 cellpadding=1 cellspacing=1>
<tr><td align=center colspan=7 bgcolor="<? echo $th_color;?>">Administrative / Professional / Technical</td></tr>
<tr>
<?
echo "<th bgcolor=$th_color2>#</th>";
echo "<th bgcolor=$th_color2>Name</th>";
echo "<th bgcolor=$th_color2>Box #</th>";
?>
</tr>

<?
require_once('include/connection.php');
?>

<?php
$lines = file('employees.txt');
$lines[] = file('employees.txt');
foreach ($lines as $line_num => $line) {
}
$i = 0;
$count = 0;
while ($i < $line_num){
$count++;
if ($line[$i] == ""){
echo "";
}
else
{
$linea = trim(htmlspecialchars($line[$i]));

//echo "\$linea = " . $count . "--" . $linea . " ";
$pieces = explode(" ", $linea);
$primero = $pieces[0];
$segundo = $pieces[1];
$tercero = trim($pieces[2]);
$cuarto = trim($primero . " " . $segundo);
/*
if ($tercero == ""){
}
else
{
echo "There is a middle initial --> \$cuarto = " . $count . "--" . $cuarto . " ";
}
*/
$i++;
?>

<?
if ($tercero == ""){
$query = "SELECT DISTINCT employee_name,employee_last,employee_box,id FROM employees WHERE
employee_name LIKE '%{$primero}%' AND
employee_last LIKE '%{$segundo}%'
ORDER BY employee_name ASC";
}
else
{
$query = "SELECT DISTINCT employee_name,employee_last,employee_box,id FROM employees WHERE
employee_name LIKE '%{$primero}%' AND
employee_last LIKE '%{$tercero}%'
ORDER BY employee_name ASC";
}

//$query = "SELECT * FROM employees WHERE CONCAT(employee_name, ' ', employee_last) LIKE '%{$linea}%' ORDER BY employee_name ASC";
//$query = "SELECT * FROM employees ORDER BY employee_name ASC";
if ($r = mysql_query ($query)) { // Run the query.

if ($n == ""){
$n = 0;
}
while ($row = mysql_fetch_array ($r)) {
$n++;
$id = $row['id'];
$employee_name = trim(ucfirst($row['employee_name']));
$employee_last = trim(ucfirst($row['employee_last']));
$employee_box = $row['employee_box'];
$employee_full = $employee_name . " " . $employee_last;

if (($i % 2) == 0){
//$td_color = $th_color;
$td_color = "#ffffff";
}
else
{
$td_color = "#ffffff";
}

if ($employee_full == $linea){
echo "<tr>";
echo "<td bgcolor=\"$td_color\">" . $n . " </td>\n";

echo "<td bgcolor=\"$td_color\">" . $employee_name . " " . $employee_last . " ";
//echo "<td bgcolor=\"$td_color\">" . htmlspecialchars($line[$i]) . " " . $employee_last . " ";
echo "<td bgcolor=\"$td_color\">" . $employee_box . " </td>\n";
echo "</tr>";

}
else
{

echo "<tr>";
echo "<td bgcolor=\"$td_color\">" . $n . " </td>\n";

echo "<td bgcolor=\"$td_color\">" . $linea . " From the email directory Does Not Match With " . $employee_name . " " . $employee_last . " From The Web and Intranet Phonebook ";
//echo "<td bgcolor=\"$td_color\">" . htmlspecialchars($line[$i]) . " " . $employee_last . " ";
echo "<td bgcolor=\"$td_color\">" . $employee_box . " </td>\n";
echo "</tr>";
//echo $i . "--" . $linea . " Does Not Match With " . $employee_name . " " . $employee_last . " ";
}

}

}
?>

<?
//echo htmlspecialchars($line[$i]) . " ";
}
}// if statement
?>

<?
mysql_close(); // CLOSE THE FILES.
?>
</table>