1

I have a database having 10000 users with unique phone numbers.And when ever a new user registers I'm trying to match all his contacts (nearly 200 to 500) with my existing data so that I can find his friends (like whats app)

Currently I m doing using foreach and its very slow, so I need help in finding a reliable solution for this which can reduce the time taken for multiple queries.

Please find the code below that i m using.

$rawdata= file_get_contents('php://input');
$Matchedcontacts = array();
$jsonArray = json_decode($rawdata);
foreach ( $jsonArray as $value)
  {

  $contactNumber = ($value->num);
  $emil= ($value->email);
  $name=mysql_query("select * from TBL_USER where  phone='".$contactNumber ."' OR email='".$email."'");
  while ($nameres= mysql_fetch_array($name))
  {
   $Matchedcontacts [] = array('username'=>$nameres['username'], 'userid'=>$nameres['c_id']);
  }

}

echo json_encode ($Matchedcontacts );

EDIT: also I'm passing a local id with the each record I'm sending. So after a match, how can I map the matched record with the particular local ID?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3108511
  • 209
  • 2
  • 7
  • You can do that in 1 query using `WHERE phone IN (phone1, ..., phoneN)'. You should build an array with all numbers and use that. And you should switch to a prepared statement with PDO or mysql to protect yourself from sql injection. See for example http://stackoverflow.com/questions/920353/can-i-bind-an-array-to-an-in-condition for more information. – jeroen May 14 '15 at 18:40

3 Answers3

2

You might want to consider importing the contacts list into a (temporary?) table, and use join.

dar7yl
  • 3,727
  • 25
  • 20
1

Assuming that phone is an INDEX of some sort, then this should be faster by passing all the values at once

$data = array();
foreach( $jsonArray as $value) $data[] = mysql_real_escape_string($value->num);
$name = mysql_query("select * from TBL_USER where  phone IN ('". implode("', '", $data) ."'");

Also, this is your obligatory mysql_ function deprecation notice. Consider switching to mysqli_

Community
  • 1
  • 1
Machavity
  • 30,841
  • 27
  • 92
  • 100
0

One very simple optimization would be to optimize your SQL Query down to a single query

First build a list of contact numbers from $jsonArray and then use that array in the following SQL Query

select email, username, userid from TBL_USER where  phone IN ( 'phone num 1', 'phone num 2' )

Also remember to escape those phone numbers so that you don't have SQL Injections!

edhurtig
  • 2,331
  • 1
  • 25
  • 26
  • thanks for the solution , let me give it a try :) Also , if i want to match if either phone or email is matching, can i do like this ? select email, username, userid from TBL_USER where phone IN ( 'phone num 1', 'phone num 2' ) OR email IN ('email 1', 'email 2' ) – user3108511 May 14 '15 at 18:55
  • Yes that would return any user that has a phone number in the phone list OR an email in the email list – edhurtig Aug 04 '15 at 02:13