Creating Associative Arrays in PHP/MySQL Query

Question

I have a column in my profileTable called "Associations"... I am trying to query profiles relative to the associations.

$sql = mysqli_query($con,"SELECT * FROM profileTable  WHERE  Keyword_ID LIKE
'%".$getKeyID."%' ORDER BY  Associations <> 'School of Engineering 
and Computer Science', Associations AND LaName ASC LIMIT $start,$end");

I am able to index profiles by education but not by Association.

    while ($row = mysqli_fetch_array($sql)) {
        $key = $row['Keyword_Name'];
        $keyID = $row['Keyword_ID'];
        $fname = $row['FirName'];
        $lname = $row['LaName'];
        $mname = $row['MName'];
        $suffix = $row['Suffix'];
        $title = $row['Title'];
        $title2 = $row['Title2'];
        $title3 = $row['Title3'];
        $education = $row['Education'];
        $education2 = $row['Education2'];
        $education3 = $row['Education3'];
        $dept = $row['Dept'];
        $phone1 = $row['PH1'];
        $phone2 = $row['PH2'];
        $email = $row['Email'];
        $photo = $row['Photo'];
        $bio = $row['BioLK'];
        $website = $row['Website'];
        $assocs = $row['Associations'];

$actions=array('School of Engineering and Computer Science'=>'Computer Science 
       and Engineering');

I need to relate the Associations with the $key(word) so if a user clicks on a keyword "Computers" it will relate to "School of Engineering and Computer Science" and index those in the "School of Engineering and Computer Science" first.

This works fine with other columns like "Education" but can't seem to get it to work with my associative array. Any ideas?

example sqlfiddle: http://sqlfiddle.com/#!2/c1802/7/0

question: why are you unpacking that row into a million variables? — Mike 'Pomax' Kamermans
– Mike 'Pomax' Kamermans, Commented Dec 16, 2013 at 18:18
no, that was the entire question. You have a data container with all your data, why would you unpack it to 20 variables instead of using the associative data array in the section of code that needs to use it? This just makes your code unnecessarily huge, without any real benefit ($title1 is just as legible as $data["title1"], for instance) — Mike 'Pomax' Kamermans
– Mike 'Pomax' Kamermans, Commented Dec 16, 2013 at 18:24
On a more getting-an-answer related note, you probably also want to post a sqlfiddle.com link to show what your data actually looks like, with the queries easy to replicate. — Mike 'Pomax' Kamermans
– Mike 'Pomax' Kamermans, Commented Dec 16, 2013 at 18:26

david strachan · Accepted Answer · 2013-12-17 22:34:50Z

2

The following query lists output ordered as required.

SELECT * FROM mediaContacts ORDER BY  associations DESC,lname ASC

Fiddle

As associations has School of Engineering and Computer Science as the last alpha record this works.

If School of Engineering and Computer Science is not the last alpha record this fails and another method is required.

answered Dec 17, 2013 at 22:34

david strachan

7,2282 gold badges25 silver badges33 bronze badges

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

1 Comment

MsNichols Over a year ago

Thanks @davidstrachan this solved my query... and isolated my association according to my clicked keyword just as expected. Thanks again!

Mike 'Pomax' Kamermans · Accepted Answer · 2013-12-16 20:52:30Z

0

You can try the following

Change

while ($row = mysqli_fetch_array($sql)) {

to

while ($row = mysql_fetch_assoc($sql)) {

It will Return an associative array that corresponds to the fetched row and moves the internal data pointer ahead.

edited Dec 16, 2013 at 20:52

Mike 'Pomax' Kamermans

54.3k17 gold badges133 silver badges182 bronze badges

answered Dec 16, 2013 at 18:51

Yogesh Pawar

3342 silver badges17 bronze badges

2 Comments

MsNichols Over a year ago

I thought this too but it instead did not return any results. With fetch_array it returns the results, just doesn't index the "Associations" first. Hmmm...

MsNichols Over a year ago

I've added a sqlfiddle at: sqlfiddle.com/#!2/c1802/7/0 if you change "assoc" back to "array" it works...

Collectives™ on Stack Overflow

Creating Associative Arrays in PHP/MySQL Query

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related