Very Simple for all but me!

[unistake]

Hi,

I have a very simple problem!

 

I have a mysql database and all i want to do is "SELECT user FROM table WHERE col1='$col2'"

and then echo the user names.

I, for some reason though am just having trouble getting the data!

 

Any help much appreciated!

Thanks

[Barand]

<?php 
$col2 = 'something';

$sql = "SELECT user FROM `table` WHERE col1='$col2'";

$res = mysql_query ($sql) or die (mysql_error());

while ($row = mysql_fetch_assoc($res))
{
    echo $row['user'] . '<br/>';
}
?>

[unistake]

OK! this is what I have so far!...

It is meant to be a sort of referral tracking program! The users have a unique 'code' .

All the people they have referred will have the same value as 'code' typed in a column called 'friend'

Therefore this program is meant to show a counter of the amount of people referred! - But it is not working!

Anyone got any ideas?! Thanks

 
<?php
  $user = $_COOKIE["user"];
  
include("db.php");

$user = $_COOKIE["user"];
  $cxn = mysqli_connect($host,$username,$password,$database)
         or die ("couldn't connect to server");
  $query = "SELECT code FROM Member WHERE user='$user'";
  $result = mysqli_query($cxn,$query)
            or die ("Couldn't execute query.");
$nrows = mysqli_num_rows($result);

while($row = mysqli_fetch_assoc($result)) {
extract($row);
}

if ($code !=0) {

$query = "SELECT * FROM Member WHERE friend='$code'";
  $result = mysqli_query($cxn,$query)
            or die ("Couldn't execute query.");
$nrows = mysqli_num_rows($result);
$referred = $nrows-1;
echo "$referred";
}
else { 
echo "0";
}
?>

[AndyB]

But it is not working!

 

And exactly what does that mean? Do you get any result? Do you get any error messages?

[unistake]

Sorry!

That means that the echo is always '0' (the else output)

 

However in my database there are columns with exactly the same VARCHARS in these two columns. no mistakes in spelling etc....

 

no error signs occur. just echoes of '0'!

 

 

[Barand]

after each "$query = ....",

 

put

 

echo $query, '<br/>';

 

See what they give

[unistake]

ok tried that but the echos are exactly like the line below

SELECT code FROM Member WHERE user='testing-user'

 

also the second does not show because of the IF statement. It just skips it on to the ELSE statement and echos "0".

 

There is definately a value in the right mysql fields.

[Barand]

what does this display

 

<?php
while($row = mysqli_fetch_assoc($result)) {
extract($row);
echo "$code <br/>";
}

[unistake]

that echos the correct value in the db field. no problem there!

I dont understand that $code does have a value with it, but for some reason skips to the ELSE statement.

[Barand]

HINT for the future:

 

If you want help debugging and someone asks you insert some debug code to see what the output is, post the output, otherwise they are just working blind.

 

I'm off to spend my time elsewhere. Good luck.

[kenrbnsn]

You have a problem with your loop, as your code now stands, the while loop will go through all the record and then do the "if". The "if" will only act on the last record retrieved, not all of them. Also, don't use extract, but use the array, $row, itself. Try this:

<?php
  $user = $_COOKIE["user"];
  
include("db.php");

$user = $_COOKIE["user"];
  $cxn = mysqli_connect($host,$username,$password,$database)
         or die ("couldn't connect to server");
  $query = "SELECT code FROM Member WHERE user='$user'";
  $result = mysqli_query($cxn,$query)
            or die ("Couldn't execute query.");
$nrows = mysqli_num_rows($result);

while($row = mysqli_fetch_assoc($result)) {

       if ($row['code'] !=0) {

          $query = "SELECT * FROM Member WHERE friend='" . $row['code'] . "'";
          $result = mysqli_query($cxn,$query)
                  or die ("Couldn't execute query.");
          $nrows = mysqli_num_rows($result);
          $referred = $nrows-1;
          echo $referred . '<br>';
    } else  
         echo "0<br>";
}
?>

 

Ken

[unistake]

The same thing happens unfortunately Ken.

Thanks for showing me that problem though

[kenrbnsn]

You are using the same variable "$result" for the two different mysqli_query() calls, you need to use different variables:

<?php
  $user = $_COOKIE["user"];
  
include("db.php");

$user = $_COOKIE["user"];
  $cxn = mysqli_connect($host,$username,$password,$database)
         or die ("couldn't connect to server");
  $query = "SELECT code FROM Member WHERE user='$user'";
  $result = mysqli_query($cxn,$query)
            or die ("Couldn't execute query.");
$nrows = mysqli_num_rows($result);

while($row = mysqli_fetch_assoc($result)) {

       if ($row['code'] !=0) {

          $query = "SELECT * FROM Member WHERE friend='" . $row['code'] . "'";
          $rs = mysqli_query($cxn,$query)
                  or die ("Couldn't execute query.");
          $nrs = mysqli_num_rows($rs);
          $referred = $nrs-1;
          echo $referred . '<br>';
    } else  
         echo "0<br>";
}
?>[/code[

Ken

[unistake]

Still echoing "0"!

Is there any other way that I can do this sort of referral thing?

All I want to do is to allow my users to make a unique referral code. So that their friends can type it in when registering.

Then the user can track his/her friends.

 

Any suggestions would be appreciated.

[AndyB]

Actually, it is very simple.

 

Step 1: show us exactly the code you are using.

 

Step 2: show us your database structure and a couple of rows from your database

 

Step 3: follow our suggestions.

[unistake]

That is what i have been doing.... this is everything.

 

<?php
  
include("db.php");

$user = $_COOKIE["user"];
  $cxn = mysqli_connect($host,$username,$password,$database)
         or die ("couldn't connect to server");
  $query = "SELECT code FROM Member WHERE user='$user'";
  $result = mysqli_query($cxn,$query)
            or die ("Couldn't execute query.");
$nrows = mysqli_num_rows($result);

while($row = mysqli_fetch_assoc($result)) {

       if ($row['code'] !=0) {

          $query = "SELECT * FROM Member WHERE friend='" . $row['code'] . "'";
          $rs = mysqli_query($cxn,$query)
                  or die ("Couldn't execute query.");
          $nrs = mysqli_num_rows($rs);
          $referred = $nrs-1;
          echo $referred;
    } else  
         echo "0";
}
?>

 

Database: Member

userID    user          friend            code

28        123name                      testcode

31        user123    testcode

 

Everything i have given you is "as is".

[BlueSkyIS]

          $nrs = mysqli_num_rows($rs);
          $referred = $nrs-1;

 

you set $nrs = mysqli_num_rows($rs); then set $referred = $nrs-1;

 

so if $nrs == 1, $referred == 0;

 

if $nrs == 1, you'll get 0 echoed either way, whether $row['code'] !=0 or not.

[unistake]

sorry there are more than one users with that code in the friend column. so it wouldn't be zero.

[BlueSkyIS]

i would add some echos to check values, debug. see what $nrows is before it hits the while loop, see what $row has each pass through.

[Barand]

I see you have shown us the $code values at last.

 

The numeric value of 'testcode' is 0

 

<?php

$code = 'test';
if ($code==0)
{
    echo 'code is zero';   // this executes
}


?>

 

Change your code to

 

if ($row['code'] != '')

[unistake]

Yeah that was it! Thanks for that!