Mysql Question

duny

I have this code to retreive parts of the sql datbase. But i want to be able to enter a name like 'waterever' on a seperate php page and have this retrieve all the names from row 'login' that have the 'watever' in the "street" row of the sql database.

<?
//Our PHP/MYSQL page.
//This script displays all data on the page from 
//the selected database table 
//MySQL Variables. Edit where necessary
$host = "localhost";
$login_name = "name";
$password = "pass";
//Connecting to MYSQL
MySQL_connect("$host","$login_name","$password");
//Select the database we want to use
MySQL_select_db("database") or die("Could not select database");
//Selects all data from table
$query = "SELECT * FROM members";
$result = MySQL_query($query);
//Loop to print each line of database
While( $rows = MySQL_fetch_array($result) ) {
$street = $rows['street'];
$login = $rows['login'];
//How the text looks on the screen
echo("You have refered $login<br>");
}
//Close the connection to MySQL
MySQL_close()
?>

swr

$street = mysql_escape_string($_GET['street']);
$sql = "select * from members where street = '$street'";
mysql_query($sql) or die(mysql_error());

You might want to find a good SQL tutorial to learn what you can do with SQL queries.

duny

I read up on queries and this is what i have so far


<?
$affname = $HTTP_SESSION_VARS['_user']['login'];
echo("$affname");
//Our PHP/MYSQL page. 
//This script displays all data on the page from 
//the selected database table 
//MySQL Variables. Edit where necessary 
$host = "localhost"; 
$login_name = "name";
$password = "pass"; 
//Connecting to MYSQL 
MySQL_connect("$host","$login_name","$password"); 
//Select the database we want to use 
MySQL_select_db("name") or die("Could not select database"); 
//Selects all data from table 
$query = "select * from members where street = '$affname'"; 
$result = MySQL_query($query); 
//Loop to print each line of database 
While( $rows = MySQL_fetch_array($result) ) { 
//How the text looks on the screen 
$login = $rows['login'];
$email = $rows['email'];
$added = $rows['added'];
echo("<table border=1 width=438 bordercolor=#FFFFFF>
  <tr>
    <td width=76 align=\"center\">$login</td>
    <td width=47 align=\"center\">$email</td>
    <td width=249 align=\"center\">$added</td>
    <td width=38 align=\"center\">
      <p align=\"center\">$1.00</p>
    </td>
  </tr>
</table>
");
}
//echo("You have a total of = <b></b><br>"); 
//Close the connection to MySQL 
MySQL_close() 
?>

I can get this to work perfectly when i take out the affname in the $query line. and add a word like self. Works great but i need it to work with the $affname.

swr

When you echo("$affname") near the top of the script, what does it display?

duny

it displays the user name

rupertbj

enclose $affname with % marks:

$query = "select * from members where street = '%$affname%' ";

dave420

Try this instead:

$query = "select * from members where street [B]like[/B] '%".mysql_escape_string($affname)."%' ";

The "like" tells MySQL to match that substring in the field. The mysql_escape_string takes out any dangerous things put in the variable. It's quite possible to attack servers this way.

duny

well i tried both of them, i read up pretty much on all i could about queries, and when i try the LIKE it pulls up every user name in the data base. That good, atleast i know it works, but i only need it to pull up the users with the

$affname = $HTTP_SESSION_VARS['_user']['login'];

in the members where street = $affname.

I can get it to work if i manualy input the name in the =' somename'";

swr

Are you sure $affname actually contains the user name? It being blank is the only thing that would cause the like query to bring up all users, and the = query to bring up none.

duny

ok i will check again, but what is the correct way to write the $query

$query = "select * from members where street = '%".mysql_escape_string($affname)."%' ";

Is this right or wrong

duny

Everyhting is fine now, i want to thank all of you who haved helped me out.

One more question, is there a way to add up all of the totals, say if the results come back with 3 enties with the $affname and they are a 1.00 a row is there a way to say you have a total of $3.00 dollars.

Nico

you may just want to run a seperate sum query based on the same information you queried previously.

Something like


$sql = mysql_query("SELECT SUM($field) FROM $table WHERE (your where clause)");

Then to get the result use:

$res = mysql_result($sql,0,0);

Nico

duny

Nothing is working

<title>Affiliate Reports</title>
<?
session_start();
?> 
<?
//Our PHP/MYSQL page. 
//This script displays all data on the page from 
//the selected database table 
//MySQL Variables. Edit where necessary 
$host = "localhost"; 
$login_name = "";
$password = ""; 
//Connecting to MYSQL 
MySQL_connect("$host","$login_name","$password"); 
//Select the database we want to use 
MySQL_select_db("") or die("Could not select database"); 
//Selects all data from table 
$affname = $HTTP_SESSION_VARS['_amember_user']['login'];
$query = "select * from members where street = '$affname' ";
$result = MySQL_query($query);
if ($result=="") echo("Sorry $affname,<br>You have not received any referrals yet. <br>Or if you haven't signed up for the affiliates system, please navigate over to <a href=\"http://datadink.net/affiliate.php\"> Sign Up</a> Page");else{ 
echo("Welcome, $affname.  <br>Here you will see how much money, the member (s) ID and the emails of the member (s) you have referred to the <b>Mobile Datadink</b>.<br><br>");
//Loop to print each line of database 
While( $rows = MySQL_fetch_array($result) ) { 
//How the text looks on the screen 
$login = $rows['login'];
$email = $rows['email'];
echo("<table border=0 width=438>
  <tr>
    <td width=76 align=\"center\"><font size=2>$login</td>
  </tr>
</table>
");
}
}
//Close the connection to MySQL 
MySQL_close() 
?>

Every thing is good...thanks, but the last post i dont understand....
Since the street is the refer name and i just want to put you have refered $number (i want to add all the streets up) and made this much $number ( same as the top). Instead the script i have up top displays all the users that have the $affname in the street table of the mysql data base. I just want to add up the number of $affnames in each one of the members that have the $affname in it.