0

I have an SQL query, in which I am converting inches to feet.

<?php  
$query ="
SELECT *
,replace (replace('<feet>'' <inches>"',
                   '<feet>', height / 12),
           '<inches>', height % 12) AS playerHeight
,abbrName
FROM leagueRosters
INNER JOIN leagueTeams AS teamInfo
ON leagueRosters.teamId=teamInfo.teamId

WHERE abbrName LIKE '".$_GET['team']."'
ORDER BY rosterId DESC
";  
$resultRoster = mysqli_query($connect, $query);  
?>

From what I understand I'm supposed to double the single quotes, which I have done

,replace (replace("<feet>"" <inches>"",
                   "<feet>", height / 12),
           "<inches>", height % 12) AS playerHeight

And I've also tried this

,replace (replace("'<feet>'" "'<inches>'",
                       "'<feet>'", height / 12),
               "'<inches>'", height % 12) AS playerHeight

Neither one works. I've tried a few other combinations, but always end up with an error on at least one line.

I followed the answer in this question - How do I escape a single quote in SQL Server? But I"m still unsure what I'm doing wrong.

ssx95351
  • 37
  • 7
  • I don't understand why you're using `replace()` in the first place, when the original string is a literal. Just use string concatenation. – Barmar Oct 20 '17 at 17:46
  • Try this: $query =" SELECT * replace ( replace('' '', '', height / 12), '', height % 12) AS playerHeight ,abbrName FROM leagueRosters INNER JOIN leagueTeams AS teamInfo ON leagueRosters.teamId=teamInfo.teamId WHERE abbrName LIKE '$_GET[team]' ORDER BY rosterId DESC "; – Nic3500 Oct 20 '17 at 17:49
  • Note: The object-oriented interface to `mysqli` is significantly less verbose, making code easier to read and audit, and is not easily confused with the obsolete `mysql_query` interface. Before you get too invested in the procedural style it’s worth switching over. Example: `$db = new mysqli(…)` and `$db->prepare("…")` The procedural interface is an artifact from the PHP 4 era when `mysqli` API was introduced and should not be used in new code. – tadman Oct 20 '17 at 18:01
  • 1
    **WARNING**: When using `mysqli` you should be using [parameterized queries](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and [`bind_param`](http://php.net/manual/en/mysqli-stmt.bind-param.php) to add user data to your query. **DO NOT** use string interpolation or concatenation to accomplish this because you have created a severe [SQL injection bug](http://bobby-tables.com/). **NEVER** put `$_POST`, `$_GET` or **any** user data directly into a query, it can be very harmful if someone seeks to exploit your mistake. – tadman Oct 20 '17 at 18:01
  • It's generally best to store values in your database in a normalized unit, like only inches, and convert to things like feet if and when that's necessary. That's also something you should consider doing in your application layer, not in the database itself. – tadman Oct 20 '17 at 18:03
  • 1
    Thank you all for the info, as someone who is learning this on my own I appreciate the feed back and information. @tadman I will study up on `bind_param`. – ssx95351 Oct 20 '17 at 21:22

1 Answers1

1

You need to escape the double quote so it doesn't end the PHP string.

$query ="
SELECT *
,replace (replace('<feet>'' <inches>\"',
                   '<feet>', height / 12),
           '<inches>', height % 12) AS playerHeight
,abbrName
FROM leagueRosters
INNER JOIN leagueTeams AS teamInfo
ON leagueRosters.teamId=teamInfo.teamId

WHERE abbrName LIKE '".$_GET['team']."'
ORDER BY rosterId DESC
";

But there's no need to use replace in the first place, just use string concatenation.

$query ="
SELECT *
,CONCAT(FLOOR(height/12), ''' ', height % 12, '\"') AS playerheight
,abbrName
FROM leagueRosters
INNER JOIN leagueTeams AS teamInfo
ON leagueRosters.teamId=teamInfo.teamId

WHERE abbrName LIKE '".$_GET['team']."'
ORDER BY rosterId DESC
";
Barmar
  • 741,623
  • 53
  • 500
  • 612
  • Ooooh ok, thank you so much @barmar for the help and information. I'm am only 4 weeks into learning SQL on my own and I used `replace` after because of the information I found here - [link]https://stackoverflow.com/questions/30078773/converting-a-number-to-feet-inches%5D – ssx95351 Oct 20 '17 at 21:17