[SOLVED] Dynamically concatenate variables without leaving a blank space or hanging comma

[HowdeeDoodee]

 

The $match1 value below is part of a Select statement. The user, by checking a checkbox on a form, determines whether the $Topic, $Subtopic, and $Theswords variables have a value or not. If the variables have a value, the variable is added to the Select statement. If a user selects all of the variables, the Select statement executes properly. However, if only one or two of the varables are selected by the user, I get a syntax error because the Select statement contains a hanging comma as shown in the Select statements below.

 

$match1 = "";
if (!empty($Topic)){
$match1 = ($Topic . ", ");
}
if (!empty($Subtopic)){
$match1 .= ($Subtopic . ", ");
}
if (!empty($Theswords)){
$match1 .= ($Theswords);
}

 

In the statement below, the user selected Topic and Subtopic as fields to search resulting in a hanging comma and the statement generates a syntax error..

 

SELECT * FROM `View2_ConcordFT` WHERE MATCH(Topic, Subtopic, ) AGAINST ('power' IN BOOLEAN MODE) AND MATCH(Topic, Subtopic, ) AGAINST ('love' IN BOOLEAN MODE) ORDER BY `Lnum` ASC

 

In the statement below, the user selected Topic and Theswords with no hanging comma as a result and the statement executes

 

SELECT * FROM `View2_ConcordFT` WHERE MATCH(Topic, Theswords) AGAINST ('power' IN BOOLEAN MODE) AND MATCH(Topic, Theswords) AGAINST ('love' IN BOOLEAN MODE) ORDER BY `Lnum` ASC

 

In the statement below, the user selected Subtopic and Theswords with no hanging comma as a result and the statement executes

 

SELECT * FROM `View2_ConcordFT` WHERE MATCH(Subtopic, Theswords) AGAINST ('power' IN BOOLEAN MODE) AND MATCH(Subtopic, Theswords) AGAINST ('love' IN BOOLEAN MODE) ORDER BY `Lnum` ASC

 

In the statement below, the user selected Theswords only with no hanging comma as a result and the statement executes

 

SELECT * FROM `View2_ConcordFT` WHERE MATCH(Theswords) AGAINST ('power' IN BOOLEAN MODE) AND MATCH(Theswords) AGAINST ('love' IN BOOLEAN MODE) ORDER BY `Lnum` ASC

 

In the statement below, the user selected Subtopic only with a hanging comma as a result and the statement does not execute

 

SELECT * FROM `View2_ConcordFT` WHERE MATCH(Subtopic, ) AGAINST ('power' IN BOOLEAN MODE) AND MATCH(Subtopic, ) AGAINST ('love' IN BOOLEAN MODE) ORDER BY `Lnum` ASC

 

In the statement below, the user selected Topic only with a hanging comma as a result and the statement does not execute

 

SELECT * FROM `View2_ConcordFT` WHERE MATCH(Topic, ) AGAINST ('power' IN BOOLEAN MODE) AND MATCH(Topic, ) AGAINST ('love' IN BOOLEAN MODE) ORDER BY `Lnum` ASC

 

How can this issue be fixed?

 

Is there, perhaps, some kind of trim statement to trim off any hanging spaces and/or hanging commas?

 

[king arthur]

There are several ways you could fix this.

 

$match1 = "";
if (!empty($Topic)){
$match1 = ($Topic);
}
if (!empty($Subtopic)){
$match1 .= ($match1=="") ? $Subtopic : ", " . $Subtopic;
}
if (!empty($Theswords)){
$match1 .= ($match1=="") ? $Subtopic : ", " . $Subtopic;
}

 

or you could do

 

$match1 = "";
if (!empty($Topic)){
$match1 = $Topic;
}
if (!empty($Subtopic)){
$match1 .= ", " . $Subtopic;
}
if (!empty($Theswords)){
$match1 .= ", " . $Subtopic;
}

if(substr($match1, 0, 1) == ",") $match1 = substr($match1, 2);

 

In the first, I have switched it around so the comma gets added at the start in the second and third cases, but only if the string is not empty. In the second, I have again added the comma at the start and then checked to see if the string has a comma at the start and if so, take the string from the third character onwards thus removing the comma and space.

[sasa]

try

<?php
$match1 = array($Topic, $Subtopic, $Theswords);
foreach ($match1 as $k => $v) if($v == '') unset($match1[$k]);
echo $match1 = implode(', ', $match1);
?>

btw

all $match is same, isn't it

[HowdeeDoodee]

Thank you for the comments.

 

OK, King I took your suggestion and tried it first because it seemed to be the easiest for someone like me on a subnovice level to understand. I tried your suggestion but I am still getting the syntax error message. Can you or someone review this and give me any suggestions? Thank you again for the replies.

 

When the user selects the Topic checkbox I am still getting an error message.

 

Here is the echo from the Select statement and the error message when the user selects only the Topic checkbox. The word Topic has a trailing comma.

 

SELECT * FROM `View2_ConcordFT` WHERE MATCH(Topic, ) AGAINST ('peace' IN BOOLEAN MODE) OR MATCH(Topic, ) AGAINST ('love' IN BOOLEAN MODE) OR MATCH(Topic, ) AGAINST ('' IN BOOLEAN MODE) OR MATCH(Topic, ) AGAINST ('' IN BOOLEAN MODE) OR MATCH(Topic, ) AGAINST ('' IN BOOLEAN MODE) OR MATCH(Topic, ) AGAINST ('' IN BOOLEAN MODE) ORDER BY `Lnum` ASC

 

Here is the error message.

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AGAINST ('peace' IN BOOLEAN MODE) OR MATCH(Topic, ) AGAINST ('love' IN BOOLEAN' at line 1

 

Here is the code to with King's suggestions to fix the hanging comma problem.

 

$match1 = "";
if (!empty($Topic)){
$match1 = ($Topic);
}
if (!empty($Subtopic)){
$match1 .= ($match1=="") ? $Subtopic : ", " . $Subtopic;
}
if (!empty($Theswords)){
$match1 .= ($match1=="") ? $Theswords : ", " . $Theswords;
}

$match2 = "";
if (!empty($Topic)){
$match2 = ($Topic);
}
if (!empty($Subtopic)){
$match2 .= ($match2=="") ? $Subtopic : ", " . $Subtopic;
}
if (!empty($Theswords)){
$match2 .= ($match2=="") ? $Theswords : ", " . $Theswords;
}

$match3 = "";
if (!empty($Topic)){
$match3 = ($Topic);
}
if (!empty($Subtopic)){
$match3 .= ($match3=="") ? $Subtopic : ", " . $Subtopic;
}
if (!empty($Theswords)){
$match3 .= ($match3=="") ? $Theswords : ", " . $Theswords;
}

$match4 = "";
if (!empty($Topic)){
$match4 = ($Topic);
}
if (!empty($Subtopic)){
$match4 .= ($match4=="") ? $Subtopic : ", " . $Subtopic;
}
if (!empty($Theswords)){
$match4 .= ($match4=="") ? $Theswords : ", " . $Theswords;
}

$match5 = "";
if (!empty($Topic)){
$match5 = ($Topic);
}
if (!empty($Subtopic)){
$match5 .= ($match5=="") ? $Subtopic : ", " . $Subtopic;
}
if (!empty($Theswords)){
$match5 .= ($match5=="") ? $Theswords : ", " . $Theswords;
}

$match6 = "";
if (!empty($Topic)){
$match6 = ($Topic);
}
if (!empty($Subtopic)){
$match6 .= ($match6=="") ? $Subtopic : ", " . $Subtopic;
}
if (!empty($Theswords)){
$match6 .= ($match6=="") ? $Theswords : ", " . $Theswords;
}

//here are the query statement in an if_else based upon how many search terms were entered by the user


if ((!empty($terms[0]))	AND (!empty($terms[1]))	AND (!empty($terms[2])) AND 	(!empty($terms[3])) AND (!empty($terms[4])) AND (!empty($terms[5])))
{
$query = "SELECT * FROM `View2_ConcordFT` WHERE MATCH($match1) AGAINST ('$terms[0]' IN BOOLEAN MODE) AND MATCH($match2) AGAINST ('$terms[1]' IN BOOLEAN MODE) AND MATCH($match3) AGAINST ('$terms[2]' IN BOOLEAN MODE) AND MATCH($match4) AGAINST ('$terms[3]' IN BOOLEAN MODE) AND MATCH($match5) AGAINST ('$terms[4]' IN BOOLEAN MODE) AND MATCH($match6) AGAINST ('$terms[5]' IN BOOLEAN MODE) ORDER BY `Lnum` ASC";
}
elseif ((!empty($terms[0])) AND (!empty($terms[1])) AND (!empty($terms[2])) AND (!empty($terms[3])) AND (!empty($terms[4])))
{
$query = "SELECT * FROM `View2_ConcordFT` WHERE MATCH($match1) AGAINST ('$terms[0]' IN BOOLEAN MODE) AND MATCH($match2) AGAINST ('$terms[1]' IN BOOLEAN MODE) AND MATCH($match3) AGAINST ('$terms[2]' IN BOOLEAN MODE) AND MATCH($match4) AGAINST ('$terms[3]' IN BOOLEAN MODE) AND MATCH($match5) AGAINST ('$terms[4]' IN BOOLEAN MODE) ORDER BY `Lnum` ASC";
}
elseif ((!empty($terms[0])) AND (!empty($terms[1])) AND (!empty($terms[2])) AND (!empty($terms[3])))
{
$query = "SELECT * FROM `View2_ConcordFT` WHERE MATCH($match1) AGAINST ('$terms[0]' IN BOOLEAN MODE) AND MATCH($match2) AGAINST ('$terms[1]' IN BOOLEAN MODE) AND MATCH($match3) AGAINST ('$terms[2]' IN BOOLEAN MODE) AND MATCH($match4) AGAINST ('$terms[3]' IN BOOLEAN MODE) AND MATCH($match5) AGAINST ('$terms[4]' IN BOOLEAN MODE) ORDER BY `Lnum` ASC";
}
elseif ((!empty($terms[0])) AND (!empty($terms[1])) AND (!empty($terms[2])))
{
$query = "SELECT * FROM `View2_ConcordFT` WHERE MATCH($match1) AGAINST ('$terms[0]' IN BOOLEAN MODE) AND MATCH($match2) AGAINST ('$terms[1]' IN BOOLEAN MODE) AND MATCH($match3) AGAINST ('$terms[2]' IN BOOLEAN MODE) ORDER BY `Lnum` ASC";
}
elseif ((!empty($terms[0])) AND (!empty($terms[1])))
{
$query = "SELECT * FROM `View2_ConcordFT` WHERE MATCH($match1) AGAINST ('$terms[0]' IN BOOLEAN MODE) AND MATCH($match2) AGAINST ('$terms[1]' IN BOOLEAN MODE) ORDER BY `Lnum` ASC";
}
echo $query;

[king arthur]

The only way that can be happening is if the second or third if statements are executing in each case. So there must be something going wrong with if(!empty($Subtopic)) or if(!empty(Theswords)). I.e. the strings are not empty but contain a space perhaps. Or maybe the comma is already appended to the string $Topic?

[king arthur]

BTW your code as it stands, appears to produce six variables $match1 - $match6 all the same.

[HowdeeDoodee]

Thanks King, your code is working. I had not pasted in part of the code above one of the queries. You are the King. Thanks. Freaks Rule.