Returning an array from a simple mysql request

[spenceddd]

Hi,

 

I am trying to return an array of rows from a table called 'portfolioItems' that contain any of the numbers which are contained within a php array (something like: [12,13,1,86]) in either colomns 'categories' or/and 'skills', each record containing a string of numbers eg '56,13,1,98'.

 

Could anybody please advise me the best way to do this?

 

I'm guessing I need to first create an array of each colomn and then loop through them both...

 

If this is right then I am still stuck as I cannot sem to return an array from the mysql table. This is the code I have written so far:

 

$query_portfolioItemSkillsCats = "SELECT skills, categories FROM PortfolioItems"; 
$portfolioItemSkillsCats = mysql_fetch_row($query_portfolioItemSkillsCats);

 

 

Thanks a lot

 

Spencer

[JAY6390]

Well you get your array, implode it so that it's comma separated:

$arraystr = implode(',', $array);

This then gives you the string

1,2,3,4,5 if the values were 1 2 3 4 and 5 for example

Then you use the where clause to check if those values are in either column

$query = "SELECT skills, categories FROM PortfolioItems WHERE (categories IN(".$arraystr.")) OR (skills IN(".$arraystr."))"

[laffin]

first how is your db table look like?

are skills and categories items numerics?

and are they unique?

 

but it sounds more like the query should be

$queryPortfolioSkillsCats ='SELECT * FROM PortfolioItems WHERE skills IN (' . implode(',',array(12,31,1,86)) .') OR categories IN ('. implode(',',array(12,31,1,86)) .')';

 

 

 

[spenceddd]

Thanks for the replies guys.

 

The table consists of many colomns, two of them being 'Skills' and 'Categories'. An example row for either colomn would be a string like: "56,13,1,98"

 

So would this

 

$queryPortfolioSkillsCats ='SELECT * FROM PortfolioItems WHERE skills IN (' . implode(',',array(12,31,1,86)) .') OR categories IN ('. implode(',',array(12,31,1,86)) .')';

 

 

loop through the comma seperated values in the colomns?

[PFMaBiSmAd]

You are asking for the intersection of two sets of data. It currently cannot be done 'simply' by using built-in mysql functions.

 

You will either need to correct your table design (storing a list of values in a column is fine if all you are doing is storing/retrieving that information or if you are searching if one of the values matches another single value) or you will need to dynamically build your query string with a separate term to check each value in the starting array.

 

FIND_IN_SET() would be the mysql function to use to find rows where a given (single) value matches one of the values in a list in a column.

[spenceddd]

thanks for that although to be honest I don't fully understand how to implement that as I am a total novice in both php and mysql.

 

I'm not sure what you mean by re-designing the table. At present the fields I intent to search through are the result of submitted data from an 'imploded' array. How best should I store the list so I can search through it using the  FIND_IN_SET() function? Do I need to change the datatype of the colomn?

 

Could you possible give an example of the code I need for the mysql query, I always seem to struggle getting the syntax working...

 

 

 

 

[spenceddd]

After doing some more reading on the IN and FIND_IN_SET functions I was thinking of setting up the code like this:

 

$categoriesArrayToCheck = array(8,56,34,18,42);
$catArray = implode(',', $categoriesArrayToCheck);
$skillsArrayToCheck = array(78,56,23,1,120);
$skillsArray = implode(',', $skillsArrayToCheck);

foreach ($categoriesArrayToCheck as $value) {
     $query = "SELECT id FROM PortfolioItems WHERE (categories FIND_IN_SET(".$categoriesArrayToCheck .")) ";
     $portfolioItemSkillsCats = mysql_fetch_array($query_portfolioItemSkillsCats);
}
foreach ($skillsArrayToCheck as $value) {
     $query = "SELECT id FROM PortfolioItems WHERE (skills FIND_IN_SET(".$skillsArrayToCheck .")) ";
     $portfolioItemSkillsCats = mysql_fetch_array($query_portfolioItemSkillsCats);
}
//I would then need to combine the two arrays into one which I will be able to work out

 

I know syntax probably isn't exactly right but can anyone tell me if the principle is correct?

 

Thanks for any feedback on this.

 

Spencer

 

[laffin]

I think this will require a database redesign.

You can do what you ask, but it will involve FULL TEXT SEARCH as well as multiple wild card searches.

If I understand correctly, I would add two more tables. to your database

 

with something like

Table PortfolioItemCategories

  pid INTEGER NOT NULL,

  category INTEGER NOT NULL,

  PRIMARY KEY (pid,category)

 

Table PortfolioItemSkills

  pid INTEGER NOT NULL,

  skill INTEGER NOT NULL,

  PRIMARY KEY (pid,skill)

 

 

where pid is the index of PortfolioItems entry

 

so you can retreive the categories/skills with a much simpler sql statement, that wont use text pattern searching. which would be a lot faster

 

[PFMaBiSmAd]

One table would work and would allow for an limited number of different types to be used -

// if there is a table setup with id, user_id, type, value (type is either skills or categories and value is the single (one) numeric value)
// you would have data like -
// id,	user_id,	type,		value
//  1	10		skills		56
//  2	10		skills		13
//  3	10		skills		1
//  4	10		skills		98
//  5	10		categories	56
//  6	10		categories	13
//  7	10		categories	1
//  8	10		categories	98

// the query would look like this -
$array = array(12,13,1,86); // starting array of values to find
$in = implode(',',$array);
$query = "SELECT user_id FROM the_table WHERE (type = 'skills' AND value IN($in)) OR (type = 'categories' AND value IN($in))";
echo $query;

[spenceddd]

Thanks to you both, thats great and gives me some more to go on.

 

I will explain slightly further how this whole thing is working.

 

Portfolio logger:

This is a form where I log my portfolio items. I can upload images, a movie, a download file, input text information and choose multiple skills and categories associated with that portfolio item.

 

I have three tables to log this information: 1) portfolioItems (which holds all the text info and the urls of images the movie and the download file and a couple of colomns which hold the ids of the associated skills and categories - this leads me onto the other two tables)

2) categories (which holds the name and id of the categories list)

3) skills (which holds the name and id of the skills list)

 

In the portfolioItems table the colomns which hold the info about the associated categories and skills will potentially hold multiple references for each - not just one. So a typical record might be: 12,90,67,3,52

 

Portfolio website

This is where I will allow potential employers of my services view my portfolio. They will be able to search my database of portfolio items by multi-selecting the available list items of 'categories' and 'skills used'.

For this reason I need to be able to query my database to reveal all the portfolio items associated with the users selection.

 

I hope that makes sense and please let me know if it changes your advice to me at all.

 

I will try to work with what you have given me so far and thank you again for your help so far.

 

By the way you can see the portfolio logger here if it helps at all: http://www.spencercarpenter.co.uk/portfolioAppFiles/simpleForm.php

 

Spencer