unistake Posted March 22, 2008 Share Posted March 22, 2008 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted March 22, 2008 Share Posted March 22, 2008 <?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/>'; } ?> Quote Link to comment Share on other sites More sharing options...
unistake Posted March 22, 2008 Author Share Posted March 22, 2008 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"; } ?> Quote Link to comment Share on other sites More sharing options...
AndyB Posted March 22, 2008 Share Posted March 22, 2008 But it is not working! And exactly what does that mean? Do you get any result? Do you get any error messages? Quote Link to comment Share on other sites More sharing options...
unistake Posted March 22, 2008 Author Share Posted March 22, 2008 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'! Quote Link to comment Share on other sites More sharing options...
Barand Posted March 22, 2008 Share Posted March 22, 2008 after each "$query = ....", put echo $query, '<br/>'; See what they give Quote Link to comment Share on other sites More sharing options...
unistake Posted March 22, 2008 Author Share Posted March 22, 2008 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 22, 2008 Share Posted March 22, 2008 what does this display <?php while($row = mysqli_fetch_assoc($result)) { extract($row); echo "$code <br/>"; } Quote Link to comment Share on other sites More sharing options...
unistake Posted March 22, 2008 Author Share Posted March 22, 2008 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 22, 2008 Share Posted March 22, 2008 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. Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted March 22, 2008 Share Posted March 22, 2008 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 Quote Link to comment Share on other sites More sharing options...
unistake Posted March 22, 2008 Author Share Posted March 22, 2008 The same thing happens unfortunately Ken. Thanks for showing me that problem though Quote Link to comment Share on other sites More sharing options...
kenrbnsn Posted March 22, 2008 Share Posted March 22, 2008 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 Quote Link to comment Share on other sites More sharing options...
unistake Posted March 22, 2008 Author Share Posted March 22, 2008 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. Quote Link to comment Share on other sites More sharing options...
AndyB Posted March 22, 2008 Share Posted March 22, 2008 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. Quote Link to comment Share on other sites More sharing options...
unistake Posted March 22, 2008 Author Share Posted March 22, 2008 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". Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted March 22, 2008 Share Posted March 22, 2008 $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. Quote Link to comment Share on other sites More sharing options...
unistake Posted March 22, 2008 Author Share Posted March 22, 2008 sorry there are more than one users with that code in the friend column. so it wouldn't be zero. Quote Link to comment Share on other sites More sharing options...
BlueSkyIS Posted March 22, 2008 Share Posted March 22, 2008 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted March 22, 2008 Share Posted March 22, 2008 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'] != '') Quote Link to comment Share on other sites More sharing options...
unistake Posted March 22, 2008 Author Share Posted March 22, 2008 Yeah that was it! Thanks for that! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.