How to select from table where name
How to select from table where name
Not the most ingenious article name, but will go with this one for now. I’m going to write a few tricks that I stumble upon working with mysql queries and the select method. This is about how to select from table where name is equal to something related in that field name or table.
A simple example would be bellow:
1 2 3 4 5 6 7 8 9 10 11 12 |
$con=mysqli_connect("localhost","username","password","database_name"); // Make sure we first have connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } // Running a simple query $result = mysqli_query($con,"SELECT * FROM table"); while($row = mysqli_fetch_array($result)) { echo $row['field1']; echo $row['field2']; } mysqli_close($con); |
The where clause
Using the where clause you can extract specific records that meet some specific criterias.
The above is a simple example where we get from table name table the field1 and field2 contents. Now this will work in almost all cases, we should not have any errors with simple data. But what if we want use the where statement? This radically complicates things, well for most advance uses these will be easy, but for those starting programing recently they will need to get used to the logic here.
A simple example using where statement.
1 2 3 4 5 6 |
// Running a simple query using Where $result = mysqli_query($con,"SELECT * FROM table where field2 = 'value'"); while($row = mysqli_fetch_array($result)) { echo $row['field1']; echo $row['field2']; } |
Again, this is a a simple example, this can get a lot more complicated with using multiple mysql operators. For example, a more complicated sql statement would be:
1 |
$result = mysqli_query($con,"SELECT * FROM table where field2 = 'value' and field3='value' and field4='value'"); |
Now I got something really tricky, for users that will want to use special characters in their queries, for example building or people names that have a quote in their names, they will encounter some errors.
To get around this, you will need to escape the sql statement, this can be done using something similar to be the bellow example:
1 2 3 |
$person = "'s something" // some name here; $person = mysqli_real_escape_string($con, $person); $result = mysqli_query($con,"SELECT * FROM table where field2 = '$person'"); |
With this, the quote from the person name will be escaped properly and query should work as expected.