I am still new to PHP and MySQL. I know this code is a little off but it’s all I found that came close to what I need to do. I’m trying to get only the user name out a row that has both a user name and password. This code will return the whole row.
How do I return only the username that match given name from the database?
$query="SELECT username, password FROM $_MEMBERS_TABLE WHERE username = 'joe'";
$result_set= mysql_query($query);
if ($result_set){
print "Fetch the first row of data.<br>";
$record= mysql_fetch_assoc($result_set);
// print_r($record);
foreach ($record as $value){
print "<em>$value </em>"; // print the row
}
print "<br>";
}
else{ die("Query failed.". mysql_error());
}
I have the username and password for joe in the MySQL database. I simply want to query the database to see if the username column has the word joe in it. I was hoping for an if-else example that will show a match or not because I’m having no luck trying to figure out how to do it.
Anyway, maybe I’m wrong but instead of using a variable in the code I use the word joe to keep it simple as I keep trying. That part should not be no big deal for someone of experience to see through. I don’t have to keep strolling to the top to change the word when testing. Of course I have access to the database but I did not think the forum need me to post the entire program to see the point. Do that make since?
coldfusion, I’m planning to learn some JAVA and C++ with MySQL but for now with PHP I’m finally getting an clue. I guest I should post this question under PHP before the day is gone. Thanks for the LIKE code. I’m sure I’ll end-up with the if-else statement if I can ever find out how-to…
I got it to burp but it seems kind of silly using so much code but it works. Now the trick is how insert the like code - - LIKE ‘%joe%’ so it return an single value.
if ($result_set){
print "Fetch the first row of data.<br>";
$record= mysql_fetch_assoc($result_set);
// print_r($record);
foreach ($record as $value){
print "<em>$value </em>";
}
print "<br>";
}
else{ die("Query failed.". mysql_error());
}
// ..................................... Added code
if ( $value ) {
print "It's a MATCH!<br>";
}
else{
print "Not in database<br>";
}
Anyway, I guest this will do for now since it works but I still hope someone will post the standard solution for PHP and MySQL.
Hear this php-desor’s, r937 code is the ONLY thing that works with my code. I tried many sinceable ways to replace it with what seems like 20 sequence that is common place, but nothing works other than the SQL Consultant code. It got to be MySQL underlying feature knowing what to do even though my foreach statement in the code returns the entire row. You can simply comment some of the code below that line and use the rest of the juice. So by right r937 code RULES… it does infact see (but maybe not return) only a single field input as I was hoping for. Some indexing will solve that problem .
I got a long way to go before the day is out but latter I will post an entire example soon. But if you play with what you see here already, you will figure it out very quickly. Guaranteed! There could be better ways but I have not seen anything yet. You can sanitize latter, but right now it all something that really works.
Thanks a ton Mr. Consultant
I do have one final question. What is %% for… It works just as well without it. I know it has importants so please explain when the first chance you get.
You are who you say you are
Thanks again
Ok, a quick Example:
$Q_insert="SELECT username, password FROM $_MEMBERS_TABLE
WHERE username LIKE '%$_P_USER%'";
$match_insert= mysql_query($Q_insert);
if ($match_insert){
$record_i= mysql_fetch_row($match_insert);
foreach ($record_i as $v_insert) {
} }
if ( $v_insert ) {
print "s-Welcome Back $_P_USER
Click here to order.<br>"; } // db match
else{
mysql_query("INSERT INTO $_MEMBERS_TABLE
(id,username,password)
VALUES ($table_Q_numrows+1,'$_P_USER', '$_P_PASS')");
print "Thank you $_P_USER, you may now order<br>";
}
mysql_free_result($match_insert);
$table_Q_numrows+1 is a trick I just came up with since this table don’t have auto_increment … I was shock that it worked the first time… My first like invention
Simply get num_of_rows before hand and use that variable+1.
w00t! Are you seriously using a LIKE to check if user is logged in? You’re kidding, right?
The % are known as wildcards, and can match a piece of text you don’t know upfront what it will be.
So LIKE “%joe%” will match “Joe”, “Joe123”, “Banjoe”, etc.
This is why it’s no good to check if a user exists and then echo their name. If there are multiple rows that match all of them would be printed.
Instead, you should use something like
$userExistsQuery = sprintf(
"SELECT 1 FROM $_MEMBERS_TABLE WHERE username='%s'",
mysql_real_escape_string($_P_USER)
);
$userExistsRes = mysql_query($userExistsQuery);
$count = myqsl_num_rows($userExistsRes);
if ($count == 1) {
// db match
print "s-Welcome Back $_P_USER
Click here to order.<br>";
} else if ($count == 0) {
mysql_query(sprintf(
"INSERT INTO $_MEMBERS_TABLE (username,password) VALUES ('%s', '%s')",
mysql_real_escape_string($_P_USER),
mysql_real_escape_string($_P_PASS)
));
print "Thank you ".htmlentities($_P_USER).", you may now order<br>";
} else {
echo 'There already is a user with the name '.htmlentities($_P_USER);
}
mysql_free_result($userExistsRes);
well, to be fair, LIKE was my idea, but it was based on this requirement –
to see if the username column has the word joe in it
later on in the thread, max said –
What is %% for… It works just as well without it.
so prehaps he doesn’t really want LIKE at all
i was waiting for him to discover that LIKE ‘%joe%’ will bring back multiple usernames, like joe, banjoe, and joebob
I’m just getting started with PHP and MySQL and this is all on my local machine that I’m learning with. I don’t know if you guys were newbee’s once upon a time but if you were and could not figure out or find a solution for a problem, how would you feel when you are force to join a forum to ask for help and the only thing they give you is piece of code or untested code and pretended they they can’t read between the line of the question, than wait around for you to find out that the code is useless for serious use. Anyway, I am excited about PHP and MySQL and I am happy that r937 gave me something just to see MySQL do what I needed after trying for weeks on my own I got no where… that is nothing to laugh at. You all been there before.
The code should be usable for serious use. The only reason why I said I didn’t test is it because there may be syntax errors in there; a comma too many, forgot an apostophe, forgot a parenthesis, etc. The logic should be sound though.
Anyway, it was not all my intention to belittle you and I’m sorry if you feel that way!
I had the feeling you were more advanced than you apparently are (or say you are).
ScallioXTX No apology needed. Just be more sensitive of new comers. You guys did still porvided something to work with and I greatly appreciate it. There is nothing worse than getting a new train-set for Christmas and never get it to go around the tracks at lease once.
