Get row numbers from mysql_fetch_array result

Question

I have pulled in the data from a mysql database using select * with the intention of using the data several times without doing repeated sql enquiries using WHERE.

Using this data I am extracting rows that contain a search element using

while($row=mysql_fetch_array($query_result)){  <<<if match add to new array>>>  }

As there are thousands of rows this is taking a longer time than I want.

I am trying to use:

$row=mysql_fetch_array($query_result);
$a = array_search($word_to_check, $row);
echo $a;

This extracts the correct sql headings but not the row number. What I want to achieve is

if $word is found in mysql_fetch_array($query_result) the add the row where it was found into the new array for processing.

Any thoughts? Thanks in advance.

yes it can. WHERE col1 = 'aaaa' OR col2 ='aaa'; There is also one good way for searching texts. You should read about full text index. And match against syntax. — Robert
– Robert, Commented Jun 6, 2013 at 12:27

Robert · Accepted Answer · 2013-06-06 12:18:25Z

2

Don't use mysql_* functions they are depracated. Use mysqli or pdo instead.

It's not wise to search in array of mysql results in php while it can be done in mysql. Let's say you have table and you want to find all numbers in number column that are greater than 5

  SELECT FROM table_name WHERE number>5

to find text you can use simple clause

  SELECT FROM table_name WHERE name = 'username'

You can also create more complex conditions.

From MYSQL manual:

WHERE clause, if given, indicates the condition or conditions that rows must satisfy to be selected. where_condition is an expression that evaluates to true for each row to be selected. The statement selects all rows if there is no WHERE clause

Check this link

edited Jun 6, 2013 at 12:18

answered Jun 6, 2013 at 12:13

Robert

20.3k6 gold badges58 silver badges85 bronze badges

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

7 Comments

John Musgrave Bolanos Over a year ago

Thanks for the quick replies...what I am doing is checking a paragraph of text for words in the database so the routine could access the database 20 times with a 'where' query. If this is multiplied by 200 users is that not going to slow things down too much?

Robert Over a year ago

you can use WHERE IN() which is for sure faster than looking for values in PHP

John Musgrave Bolanos Over a year ago

I originally used sql accessing to get the appropriate rows but then convinced myself it was too much data accessing so went for the hold data and recycle option. So the database option is much faster even if there are hundreds of queries at the same time rather than each user getting data once and using it?

Robert Over a year ago

You can use prepared statements to improve perfomace and it's only about binding values than giving whole query to Db. So in this case it'll be faster.

John Musgrave Bolanos Over a year ago

I'm using the following with an array containing the table names. I could use this with a WHERE statement further in the script to access the database for specific information.$count=0;while($count<count($sql_word_query)){$select=$select."SELECT * FROM $sql_word_query[$count]";$count++;if($count<count($sql_word_query))$select=$select." UNION ALL ";}$result=mysql_query($select);$GLOBALS['query']=$select;

|

Bere · Accepted Answer · 2013-06-06 13:16:37Z

0

If you want to limit the query to only once, fetch all the results into temporary array and do the search from it like below

<?php
$all_rows=array();
$match_rows=array();
$i=0;
$limit=100000;
while($row=mysql_fetch_array($query_result)){  
   $all_rows[]=$row;
   if($i % $limit == 0){  // this part only functions every 100,000 cycles.
      foreach($all_rows as $search_row){
        if(array_search($word_to_check, $search_row)
            $match_rows[]=$search_row;
        } 
      $all_rows=array();//reset temporary array
   }
   $i++;
}

//This solution assumes the required word can be found in mulitple columns

edited Jun 6, 2013 at 13:16

answered Jun 6, 2013 at 12:34

Bere

1,7472 gold badges17 silver badges23 bronze badges

2 Comments

John Musgrave Bolanos Over a year ago

I was thinking along those lines...I did the first step and the data being processed - the temp array - has jumped from 1.5mb to 46.5 mb

Bere Over a year ago

@JohnMusgraveBolanos I think my updated answer addresses the memory issue.

Collectives™ on Stack Overflow

Get row numbers from mysql_fetch_array result

2 Answers 2

7 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related