MySQL only selecting first word of field

Question

I'm trying to populate a form as such:

$email = $_SESSION["email"];
$result = $mysqli->query("SELECT * FROM patient WHERE email = '$email'");
$user = $result->fetch_assoc();
$firstName = $user["firstName"];
$lastName = $user["lastName"];
$bday = $user["bday"];
$address1 = $user["address1"];
$address2 = $user["address2"];
$city = $user["city"];
$state = $user["state"];
$zipCode = $user["zipCode"];
$phone = $user["mobilePhone"];
$contactMethod = $user["contactMethod"];
$reminders = $user["reminders"];
$updates = $user["updates"];

I'm then using echo like this:

<input type="text" name="address1" <?php echo 'value=' . $address1; ?> required />

It works for every field except the one shown. It only gets the first word of the column. For example, "123 First Street" only populates the input field as "123". If I delete the space and set the column value to "123FirstStreet" the whole thing populates. So I'm guessing that mySQL is automatically using a space delimiter to separate values within a column. Is there a way to fetch entire column? Thanks in advance.

Look at the *actual* HTML source generate. The problem should be quite .. apparent. Closing because the issue is a simple self-discoverable mistake. However, may want to search for how to (safely) write HTML attributes in PHP: not only will it "make the code work", but it will also prevent malicious HTML injection. — user2864740, Jun 25 '18 at 22:18
Also, read up about parameterized queries to avoid *SQL Injection*. — user2864740, Jun 25 '18 at 22:22
Duplicate: https://stackoverflow.com/q/7753448 (for HTML injection/writing and "this problem") also see https://stackoverflow.com/questions/60174 (for the SQL injection) — user2864740, Jun 25 '18 at 22:51
Your script is wide open to [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) in either the `MYSQLI_` or `PDO` API's — RiggsFolly, Jun 25 '18 at 22:52
Do you get the same result when doing the same query directly in mySQL? — John T, Jun 25 '18 at 23:17

hcheung · Accepted Answer · 2018-06-26T06:44:52.240

Your code

<input type="text" name="address1" <?php echo 'value=' . $address1; ?> required />

will generate an incorrect html tag as:

<input type="text" name="address" value=123 First Street  required />

Try change it to:

<input type="text" name="address" value="<?php echo $address; ?>" required />

The best practice to avoid such mistake is to write the complete html template/tag first before inserting the php code.

Updated

As pointed out by @user2864740, it is better to convert the special character that occurred in an input string into the form of HTML character entities.

<input type="text" name="address" value="<?php htmlentities($address1); ?>" required />

@user2864740, data escaping is not what OP asking for, and is not part of this question or problem. — hcheung, Jun 26 '18 at 06:02

score 0 · Answer 2 · edited Jun 26 '18 at 06:44

0

MySQL is not using any space delimiter please check the populated data in your database and the INSERT method you may made a typo.

edited Jun 26 '18 at 06:44

Mike Doe

16,349
11
65
88

answered Jun 25 '18 at 22:18

Abdelkader Mh

1
1

The problem is it generates invalid HTML, when the field contains spaces (or pretty much any non-alphanumeric character). The database "probably" already contains the correct data – user2864740 Jun 25 '18 at 22:19
The database has the correct data. Is there a way around the invalid html? Like to select the individual words and manually separate them like $word1 . ' ' . $word2 . ' ' . $word3 – Adam Johnston Jun 25 '18 at 22:22
@AdamJohnston try var_dump($user) you may find what missing. – Abdelkader Mh Jun 25 '18 at 22:28

MySQL only selecting first word of field

2 Answers2