How to GET SQL row using Textarea to type the ID or NAME for Row or ID?

[Miklo]

Hello everyone!

 

I am setting up a simple "Textarea" like a search-field for my forum users. So our users can get their info and info directly and change it if they wanted to. So by typing their usernames in the "textarea" they users should get ther Username and Email. Right now I am using a single Table with 3 columns called "ID", "USERNAME", "EMAIL"!

 

I have tried a few methods but I simply cannot make it work! If I can get the info by typing the ID only, thats also fine.. But then our users cannot see their ID's usually so I really need some professional help to make the Textarea work as a search fieldm, and I wish some one could provide me a simple code.

 

Right now I am working on this code and after some time I gave up!

<?php
include("sqldb.php");
if (isset($_GET['username'])) {
    $id = (int) $_GET['username'];
}
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
    or die ('Error connecting to mysql');
mysql_select_db($dbname);

$result = mysql_query("SELECT * FROM users WHERE username = '".$_GET['username']."'")
or die(mysql_error());  

// keeps getting the next row until there are no more to get
while($row = mysql_fetch_array( $result ))
{

        echo '<th>Username</th><td>';
        echo $row['username'];
        echo '</td></tr>';
       
        echo '<tr><th>Email</th><td>';
        echo $row['email'];
        echo '</td></tr>';

        echo '</tbody></table>';
         echo '</td></tr>';
}
?>
    <form action="" class="form" method="GET">
<textarea id="id" name="id" rows="5" style="width: 200px;"><?php $_GET['username']; ?></textarea>
<input type="submit" name="submit" id="submit" value="Send">
    </body>
</html>

I tried to use the POST and GET method in my html, but that didnt work either! I have tried arround 10 different jquery methods which didnt help either!

 

Thank you very much in advance...

Edited September 8, 2013 by Miklo

[Miklo]

Updates: (Still not done)

 

I got some help by a friend and realised I was doing it all wrong, For example my friend is using the LIKE method and so far I only see the ID's when the script loads, I will ofcourse fix it later but I cant make the the SEARCH FIELD work. I tried to add a button and used a form with POST and GET!

 

Here is Screenshot:

 

And this is the Code:

<input type="text" name="address">
   <input type="hidden" name="username" value="username"/>
    <select name="email">
        <option selected="selected"> </option>

<?php
include("sqldb.php");
$conn = mysql_connect($dbhost,$dbuser,$dbpass)
    or die ('Error connecting to mysql');
mysql_select_db($dbname);
        $order = "SELECT * FROM users ORDER BY id" or die (mysql_error());
        $result = mysql_query($order);  
        while($data = mysql_fetch_array($result))
        {
         echo ("<option> $data[username] </option>");
        }
       
if(isset($_POST['email']))
{
$username = $_POST['username'];
$email = $_POST['email'];

$query = "SELECT * FROM users WHERE $username LIKE '%".$email."%'";

echo "<tr><td colspan='8'>$num_rows Results Found</td></tr>";
while($data = mysql_fetch_array($result))
{
echo("<tr>
    <td>$data[username]</td>
    <td>$data[email]</td>
    </tr>");
}
}
?>

This has been helpfull for me and I have made many changes to it. And I need help to simply add a button without ruining it, because I am really not good in php!

 

Thank you in advance

Edited September 8, 2013 by Miklo

[MargateSteve]

I am far from an expert but if I understand what you are trying to do, it seems to be a bad way to do it.

 

If you are suggesting that all someone needs to do is enter their username and then be able to edit their details, what is stopping someone from entering someone else's username and editing their details?

 

If it was me I would create a simple log-in script (or if possible use the session details from the forum if it is a 3rd party one). Once the user is logged in, assign their ID to a variable and only allow them to edit the account where the ID matches.

 

Steve

[Irate]

Your script is vulnerable to SQL injections, always escape user data.

[priyankagound]

You can use Ajax.

Create the following .html page:

<html>

<head>

<script>

function showData(str)
{
if (str=="")
{
document.getElementById("ajax-content").innerHTML="";
return;
}

// Code for IE7+, Firefox, Chrome, Opera, Safari
if (window.XMLHttpRequest)
{
xmlhttp=new XMLHttpRequest();
}

// Code for IE6, IE5
else
{
xmlhttp=new ActiveXObject("Microsoft.XMLHTTP");
}

xmlhttp.onreadystatechange=function()
{
if (xmlhttp.readyState==4 && xmlhttp.status==200)
{
document.getElementById("ajax-content").innerHTML=xmlhttp.responseText;
}
}

xmlhttp.open("GET","showData.php?id="+str,true);

xmlhttp.send();
}

</script>

</head>

<body>

<form>

<select name="news" onchange="showData(this.value)">
<option value="">Select ID:</option>
<option value="1">1</option>
<option value="2">2</option>
<option value="3">3</option>
</select>

</form>

<div id="ajax-content"></div>

</body>

</html>

And the following .php script (showData.php in my example):

<?php

// Receive variable from URI
$id=$_GET["id"];

// Connect to your database
$con = mysql_connect('localhost', 'user1591005', 'stackOverflow');
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

// Select your database
mysql_select_db("myDatabase", $con);

// Select all fields from your table
$sql="SELECT * FROM news WHERE id = '".$id."'";

$result = mysql_query($sql);

while($row = mysql_fetch_array($result))
{
echo "<input type='text' value='" . $row['title'] . "'>";
echo "<textarea>" . $row['content'] . "</textarea>";
}

// Close the connection
mysql_close($con);

?>

[Barand]

:code_tags:

[Irate]

Also, it is a bad habit to break variable scope by declaring "xmlhttp" without using the var keyword in JavaScript.

 

And, to make things even easier, I recommend that everyone has jQuery on their server when using Ajax requests.

It's easier. A lot easier.

[Silverback3]

Why use $_GET['username'] in a database query when you have already declared and set a variable called $id ???

 

Also try to add caution on your code by stripping tags etc

<?php

$username = "";

if(isset($_POST['submit'])){
    $username = htmlspecialchars(strip_tags($_POST['username'])); //clean user in put the assign it to a variable
}

$SQL = "SELECT * FROM user WHERE username = '$username'"; //atleast here $username is clean on your code you risk injection

if($username){
    if($db_found){ 
        $result = mysql_query($SQL, $db_handle);
        if($result){
            //the idea goes on
        }
    }
}

?>

Edited September 23, 2013 by Silverback3

[Miklo]

Thank you so much guys! I just came online after some time so I apologise for late reply! I will be trying the methods added above, and I will be back to leave if it worked for me! I really appereciate each and every answer from the bottom of my heart! I really knew you guys were great on here! God bless, Will be back

[Miklo]

Ok so I am back after trying the methods used by @pryankagound code and @Silverback3:

 

@pryankagound, The method was good but I cant use it as long as jquery is involved. But thanks a lot for your time adding the code. I would be appreciated if you could use the "Code" tags for next time because it was hard for me to figure out which part belonged to who.. lol

 

 

@SilverBack3, I loved your method, but since I am very new I did not understand why some of the $variables were added without any code attached to it. For example on the lines:

• if($db_found){ >> Where is the $db_found part, and how to add it?
• $result = mysql_query($SQL, $db_handle); >> What is the $dbhandle for?

Thats all and so far I couldnt make it work.. I appreciated all your help guys, And hope that I could get the last part done.. I think Silverback came witht he best option.

[Barand]

Is this what you are trying to do?

<?php
$db = mysqli_connect(HOST, USERNAME, PASSWORD, DATABASE); // i'm using predefined constants

if (isset($_GET['id']) && $_GET['id'] != '') {
    //
    // find selected user
    //
    $id = $_GET['id'];
    $userid = $name = $email = '';
    $sql = "SELECT id, username, email
            FROM users
            WHERE id = ?";
    $stmt = mysqli_prepare($db, $sql);
    mysqli_stmt_bind_param($stmt, 'i', $id);
    mysqli_execute($stmt);
    mysqli_bind_result($stmt, $userid, $name, $email);
    mysqli_fetch($stmt);
    $found =  "ID: $userid<br>Name: $name<br>Email: $email<hr>";

    mysqli_stmt_free_result($stmt);    
}

/************************
* build dropdown of users
*************************/
$sql = "SELECT id, username
        FROM users
        ORDER BY fullname";
$result = mysqli_query($db, $sql) or die($db->error);

$dropdown = "<select name='id'>\n<option value=''> - select name -</option>\n";

while (list($id, $name) = mysqli_fetch_row($result)) {
    $dropdown .= "<option value='$id'> $name</option>\n";
}

$dropdown .= "</select>\n";
?>
<html>
<body>
    <?php
        echo $found;
    ?>
    <form method="get">
        <h3>Users</h3>
        <?php
            echo $dropdown;
        ?>
        <br><br>
        <input type="submit" name="btnSubmit" value="Submit">
    </form>
</body>
</html>

As you are just learning stop using the mysql library and learn mysqli (improved). Next time you upgrade your php version you will find the mysql library is no longer there.

 

In the above I have given an example of using prepared statements to prevent injection when using user inputs in your query (ie GET, POST, COOKIE data) and also a method just using mysqli_query().