How to find out automatically the numeric index of an specific MySQL column in PHP?

Question

I´m new to PHP and SQL and it´s the first time I post here, hope I do it right =)

I need to find out the numeric index from an certain field in my database. Thing is I´m not done with the database structure yet, so for this specific field I´d like to have it automated so I won't have to change the code every time I add or remove columns.

I also need it to have BOTH index types so I can call the columns by name in part of the code and by index for automated parts of it.

I´ve managed to achieve the result I wanted trough an very ugly code, because all the array_search and array_keys I´ve tried didn´t work out for some reason.

Here is the snippet of my working but ugly code. Do you have an more elegant solution?

Thanks in advance for your answers, and for all I´ve learned so far from reading other peoples questions!

$dadosRes = mysqli_query($con, "SELECT * FROM Alunos WHERE Id=1");
$student = mysqli_fetch_array($dadosRes) or die(mysql_error());

$c = 0; // This block is to find out where the column "cont1" is and assign it to $cont1
$d=array_keys($student);
$cont1=0;

foreach ($student as $a=>$b){              
if ($a==='cont1') {
$cont1=$d[$c-1];
}

$c++;
}

for ($i=$cont1; $i<$cont1+12; $i+=3) { // Displays the students contacts, each in up to 3 rows
if ($student[$i]) {
echo "<p><ul><li>".$student[$i]."</li>";
if ($student[$i+1]) echo "<li>".$students[$i+1]."</li>";
if ($student[$i+2]) echo "<li>".$students[$i+2]."</li>";
echo "</ul></p>";
}
}

I think it is easier if explain what $student contains and what $dadosRes. For me it sounds like you are trying to solve the wrong problem — WizKid, Jun 01 '14 at 23:02
`$cont1=$d[$c-1];` gives the name of the column preceding `cont1`. — bloodyKnuckles, Jun 01 '14 at 23:12
Edited the question to include $dadosRes, it´s the resources from the database. What I´m trying to achieve is a better way to come up with $cont1, one that does´t have to go trough all the columns. Going to specify that on the question too, thanks! — Tomaz Fernandes, Jun 01 '14 at 23:13
don't use `mysql_fetch_array`, but instead `mysql_fetch_assoc` which gives you the columns *by name*. — didierc, Jun 01 '14 at 23:15
Thing is I need the number index to make the rest of the code work, as in echo "
What are you wanting to do with the column index. I don't see how you're using that in the last loop. — bloodyKnuckles, Jun 01 '14 at 23:29
I have 4 contact fields, each of which containing 3 fields of information, all of which can have data or not. They are as 12 columns in the database. The idea is to loop through it so that if the first field has data, it looks the subsequent two for data too, and then the second field, and so on until all data is displayed. I also have other columns before regarding other data from the students, and that number can change, that´s the need for an automated index finder... — Tomaz Fernandes, Jun 01 '14 at 23:36
The [$c-1] part is because the preceding element is the actual index number I´m looking for in the BOTH array. — Tomaz Fernandes, Jun 01 '14 at 23:42

score 1 · Answer 1 · answered Jun 01 '14 at 23:02

1

Finding the numeric index:

$student = mysqli_fetch_array($dadosRes,MYSQLI_ASSOC) or die(mysql_error());
$numericIndex = array_search("cont1",array_keys($student));

Giving MYSQLI_ASSOC will force the query to fetch only the associative keys; otherwise it will give you both numeric and associative array.

answered Jun 01 '14 at 23:02

Well, this gave me the number I wanted, but now the rest of the code above, where I used $cont1 as base index, doesn´t work... – Tomaz Fernandes Jun 01 '14 at 23:20
I see you're using `$student` array (which is an associative now) as numeric array. Consider doing this before the loop: `$nstudent = array_values($student);` which will convert to numeric array; Then use the `$nstudent` inside the loop. – Jun 01 '14 at 23:43
Gonna try that, but please let me ask you: is there any reason why array_search doesn´t work with BOTH arrays? It returns 0 to me. – Tomaz Fernandes Jun 01 '14 at 23:53
I´m asking this because your solution would mean duplicated array data, one array indexed by numbers, the other associative. Seems better to have just one with both indexes, isn´t it? – Tomaz Fernandes Jun 01 '14 at 23:58
array_search returns `>=0` if it finds a key, otherwise it returns `FALSE` if not. You should check it like this `!==FALSE`. – Jun 02 '14 at 00:00
But if I type `$cont1 = array_search("cont1",array_keys($aluno))` it returns me 0, even though "cont1" is there for sure... – Tomaz Fernandes Jun 02 '14 at 00:04
Then i assume the "cont1" is the first element in the array? What's the problem of the 0? Its a valid numeric index. You should print_r() to validate it's the first. Otherwise it's a problem. – Jun 02 '14 at 00:06
It´s not, it´s actually the 11th column in the database... I´ve tried with other column names as well, all of them returned 0. – Tomaz Fernandes Jun 02 '14 at 00:11

score 0 · Accepted Answer · edited May 23 '17 at 10:32

0

PHP function, array_search in this case, requires the third parameter set to TRUE:

<?php

$dadosRes = mysqli_query($con, "SELECT * FROM Alunos WHERE Id=1");
$student = mysqli_fetch_array($dadosRes, MYSQLI_BOTH) or die(mysql_error());

$student_keys = array_keys($student);
$cont1 = $student_keys[array_search("cont1", $student_keys, true) - 1];

for ($i=$cont1; $i<$cont1+12; $i+=3) { // Displays the students contacts, each in up to 3 rows
    if ($student[$i]) {
        echo "<p><ul><li>".$student[$i]."</li>";
        if ($student[$i+1]) echo "<li>".$student[$i+1]."</li>";
        if ($student[$i+2]) echo "<li>".$student[$i+2]."</li>";
        echo "</ul></p>";
    }
}
?>

MYSQLI_BOTH is used in the mysqli_fetch_array function because first the array is searched for the column name, then later the array is looped over with numeric indexes. This results in the $student array having integer 0 as the first element and therefore causing array_search to choke. Adding true as the third parameter causes a === comparison unchoking the function. :) (Explanation: PHP array_search consistently returns first key of array )

edited May 23 '17 at 10:32

Community

1
1

answered Jun 01 '14 at 23:10

bloodyKnuckles

11,551
3
29
37

Didn´t do the trick... But $dadosRes is a single row array (id=1), why use "while"? – Tomaz Fernandes Jun 01 '14 at 23:55
I wasn't able to get array_search to find the column name, so I put a loop in there instead. And I pulled out the while loop, as you point out, it's not necessary. – bloodyKnuckles Jun 02 '14 at 00:22
That´s pretty much what I did before, it works, but have you got any clue why doesn´t array_search find the column name? After all I guess that´s what my question should have been in the first place, I´m going to research that specifically... – Tomaz Fernandes Jun 02 '14 at 00:28
It appears if the first value of the array is integer 0, then array_search returns 0, regardless that the matching element is further along in the array. http://stackoverflow.com/questions/13259614/php-array-search-consistently-returns-first-key-of-array – bloodyKnuckles Jun 02 '14 at 01:07
I just found out that my first array element is exactly INT(0)! Don´t know why, though, I thought it should be the 'Id' column... Going to try unsetting it. – Tomaz Fernandes Jun 02 '14 at 01:18
I've been using MYSQLI_BOTH in the fetch_array, then pulling the keys, so the first element will be integer zero. But I think that's necessary if you want to loop over the columns later with numeric indexes. The integer zero chokes array_search UNLESS you give TRUE as the third value. Example updated. – bloodyKnuckles Jun 02 '14 at 01:25
That TRUE made it! The only thing to add to your example is that $cont1 actually has the index number of the BOTH string, it has to be re-indexed, such as in `$keys=array_keys($student); $index = array_search("cont1", $keys, true) - 1; $cont1=$keys[$index];` – Tomaz Fernandes Jun 02 '14 at 01:40
I just updated the response. Look at the "cont1" assignment line: I found the $student_keys index of "cont1", backed up one array element, then got the $student_keys value, which is the numeric index of "cont1". The way it is now, it's working for me. Let me know if it's still not working on your end. – bloodyKnuckles Jun 02 '14 at 01:44

How to find out automatically the numeric index of an specific MySQL column in PHP?

2 Answers2