php explode find array key

Question

I have this mysql query (using pdo) :

SELECT DISTINCT CONCAT (nompropre, ' ', Auteur, ' de ', localite) AS auteur FROM Actes ORDER BY nompropre

It is used to create a select menu. Trying to analyse the input received from a form (select) :

<option value=" abbé de Val-Saint-Lambert"> abbé de Val-Saint-Lambert</option>
<option value=" abbé de Waulsort"> abbé de Waulsort</option>
<option value="Arnoul III comte de Looz et Chiny">Arnoul III comte de Looz et Chiny</option>
<option value="Arnoul III seigneur de Looz et Chiny">Arnoul III seigneur de Looz et Chiny</option>
<option value="Jean de Wasnes chevalier de ">Jean de Wasnes chevalier de </option>
<option value="Adam évêque de Morinie">Adam évêque de Morinie</option>

Then I do this :

$parts =  explode(' ', $_GET['nompropre']);

What I need to do is rebuild the mysql fields that I first concatenated so I can end up with such a query (corresponding to last select in example above) :

SELECT * FROM Actes, Bibliographie WHERE id = idBiblio AND nompropre = 'Adam' and Auteur = 'évêque' AND localite = 'Morinie';

For this, I could isolate a string (abbé || comte || seigneur) and make this Auteur (for the purpose of the mysql query for get results)

Everything that is before that string should become nompropre

the word "de" if it exists after that string should be removed and

everything after the word "de" (removed) should become localite for the mysql query.

maybe a preg_match and preg_replace would do the job.

Your database seems to be badly designed. You should store all authors in a separate table with a unique id and use this id in the Actes table (and remove all other authors related columns). — Casimir et Hippolyte
– Casimir et Hippolyte, Commented May 7, 2015 at 11:29
not sure because I also need separate queries on the place where they are from (localite) and their quality (évêque, abbé, seigneur, comte, ....). So I would have the problem I describe anyway, also if I had put the author in a separate table... — thiebo
– thiebo, Commented May 7, 2015 at 11:42
The problem you describe is a false problem, with the design I suggest: 1) you don't need to use DISTINCT, you only need to get all records (that are unique) of the author table. 2) concatenation is not a job for mysql but for php. Once you have separated fields (nompropre, localite...) for each records you can easily concatenate them the way you want with php. — Casimir et Hippolyte
– Casimir et Hippolyte, Commented May 7, 2015 at 11:47
I suggest you to take a look at a tutorial about how to build a relational database (there are a lot of tutorials about that, for example here: openclassrooms.com/courses/… (part I & II)). This will make your life more easy. — Casimir et Hippolyte
– Casimir et Hippolyte, Commented May 7, 2015 at 11:50

Marcin Wasilewski · Accepted Answer · 2015-05-07 11:45:03Z

1

I don't speak French so it was hard for me to figure out exactly what you need but from what I understood you want a regular expression like this:

$string = "Arnoul III comte de Looz et Chiny";
$pattern = "/^(.*)(abbé|comte|seigneur|seigneur|évêque|chevalier){1} de (.*)$/";
$nompropre = preg_replace($pattern, "$1", $string);
$auteur = preg_replace($pattern, "$2", $string);
$localite = preg_replace($pattern, "$3", $string);

answered May 7, 2015 at 11:45

Marcin Wasilewski

7451 gold badge10 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

alex · Accepted Answer · 2015-05-07 13:05:15Z

I hope I have understood your question.

I assume you want to store some options in your select which are a composite of different data from the database, and have the user pick from them.

I advise a number of things -

1) Don't bother with CONCAT in sql, its better to do that with application code in you php not in the database.

2) Make things easier on yourself, the option's value isn't visible so perhaps you could build an associative array of the columns and values in php, call json_encode on the array, and put the resulting string as the value in the tag. You can then json_decode and get your array back when you want to get the data out, so you don't have to string parse.

3) If you could refactor your database so all you need to store in the value is an id of a database row with the relevant data.

At least I would not try to solve such issues with string parsing.

Collectives™ on Stack Overflow

php explode find array key

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related