Member Avatar for ankit1122

index.php code

<html>
    <head>
        <meta charset="UTF-8">
        <title>Our Local site</title>

    </head>
    <body>
        <form action="welcome.php" method="post">
            <input id="t1" name="t1"type="text" placeholder="table name"><br>
          <input id="s1"type="text" name="skill[]"placeholder="skill or item or spec">
        <input id="s2" type="text" name="skill[]" placeholder="skill or item or spec">
        <input id="s3" type="text" name="skill[]" placeholder="skill or item or spec">
        <input id="s4"type="text" name="skill[]" placeholder="skill or item or spec">
        <input id="s5"type="text" name="skill[]" placeholder="skill or item or spec">
        <input id="s6"type="text" name="skill[]" placeholder="skill or item or spec">
        <input id="s7"type="text" name="skill[]" placeholder="skill or item or spec">
        <input id="s8"type="text" name="skill[]" placeholder="skill or item or spec">
        <input id="s9"type="text" name="skill[]" placeholder="skill or item or spec">
        <input id="c1"type="text" name="city[]"placeholder="city">
       <input id="c2"type="text" name="city[]"placeholder="city">
        <input id="c3"type="text" name="city[]" placeholder="city">
        <input id="c4"type="text" name="city[]" placeholder="city">
        <input id="c5"type="text" name="city[]" placeholder="city">
        <input id="c6"type="text" name="city[]"placeholder="city"><br>
        <input id="r1"type="text" name="region[]"placeholder="Region in city">
        <input id="r2"type="text" name="region[]" placeholder="Region in city">
        <input id="r3"type="text" name="region[]" placeholder="Region in city">
        <input id="cn1"type="text" name="country[]" placeholder="country">
        <input id="cn2"type="text" name="country[]" placeholder="country">
        <input id="cn3"type="text" name="country[]" placeholder="country">
        <input id="l1"type="text" name="lat"placeholder="Latitude in degrees">
        <input id="l2"type="text" name="long"placeholder="Longitude in degrees">
        <input type="submit">
       </form>
    </body>
</html>

wel.php code----->>>`

<?php
$count = 0;
    foreach($_POST as $item)
    {
    $count += (is_array($item)) ? count($item) : 1;
    }
    echo "Items = $count";


$skills = explode(" ", $_POST["skills"]);
$table = explode(" ", $_POST["table"]);
$city = explode(" ", $_POST["city"]);
$region = explode(" ", $_POST["region"]);
$country = explode(" ", $_POST["countrys"]);


print_r($table);

?>

i just wanna create different views for each and every string of arrays...
i have a column of skills in which skills of various people are added like p1 has skills of "cpppl,javapl,flashpl,cook,runner,businessman,bodybuilder".u can say all the different things a man can do is placed in a single column..

how can i query this same column for multiple world....like cpppl and javapl simultaneously???

i am thinking of using nesting by using views but there should be a more faster way available in mysql if i could know...?

  • 3 Contributors
  • 3 Replies
  • 381 Views
  • 1 Hour Discussion Span
  • Latest Post Latest Post by Excizted

Dani AI

Generated

Storing lots of skills in one text column is the root problem here. Quick fixes like the substring searches suggested by and will work for one-off queries, but they are fragile (substring collisions) and slow at scale (leading-wildcard LIKE). If you want correct, maintainable, and fast queries—especially "rows that have both skill A and skill B"—the right approach is to stop treating skills as a comma list and model them properly.

A simple normalized schema fixes almost everything:

CREATE TABLE people (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

CREATE TABLE skill (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE person_skill (
  person_id INT NOT NULL,
  skill_id INT NOT NULL,
  PRIMARY KEY (person_id, skill_id),
  FOREIGN KEY (person_id) REFERENCES people(id),
  FOREIGN KEY (skill_id) REFERENCES skill(id)
);

Migration and querying notes: split the current CSV field server-side (your form already sends skill[], which makes this easy), insert distinct skill names into skill, then populate person_skill. To find people who have all N requested skills, join to the junction table and require N matches:

SELECT p.*
FROM people p
JOIN person_skill ps ON ps.person_id = p.id
JOIN skill s ON s.id = ps.skill_id
WHERE s.name IN ('cpppl','javapl')
GROUP BY p.id
HAVING COUNT(DISTINCT s.id) = 2;

(Adjust the IN list and the number in HAVING to match how many skills you require.)

Alternatives: a FULLTEXT index with boolean mode can be faster than repeated LIKEs for natural-language style queries (use +term for AND), but it has caveats (stopwords, minimum word length). A MySQL SET column is only reasonable if the skill domain is tiny and fixed. Final recommendation: normalize, add proper indexes, use prepared statements when inserting, and use the JOIN+HAVING pattern to do exact multi-skill matches reliably and performantly.

Recommended Answers

All 3 Replies

Member Avatar for delta_frost

SELECT * FROM people WHERE skills LIKE '%cpppl%' OR skills LIKE '%javapl%'
That should do it.

Member Avatar for ankit1122

no i want to use and opeartor..
please dont guess and give correct answers...

Member Avatar for Excizted

I too think you need to use the OR operator. You can still use the and operator, like this

SELECT * FROM table WHERE field1 = 'something' AND field2 = 'somethingelse' AND (field3 LIKE '%word1%' OR field3 LIKE '%word2%')

Edited by Excizted
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.