how to make a mysqli function for a search engine?

Question

how to make a mysqli function for a search engine?

Navigation

#1 by (1 votes)

1

I want to make a contact search engine of those who are your friends only and not others as well as whatsapp does but I do not know how to make a SQL table to make the query.

I passed this code but it does not work for me example:

$SQL_DATA = "SELECT * FROM ".T_USERS." WHERE userID LIKE '%$data_search%' OR userID IN(
    SELECT user_one as user_id FROM ".T_FRIENDS." WHERE user_two = $userID UNION
    SELECT user_two as user_id FROM ".T_FRIENDS." WHERE user_one = $userID 
    ) ORDER BY userID DESC LIMIT 10";

My tables

CREATE TABLE 'db_user' (
  'userID' int(11) NOT NULL,
  'user' varchar(170) DEFAULT NULL,
  'pass' varchar(255) DEFAULT NULL,
  'Wall' varchar(10000) NOT NULL,
  'website' varchar(1000) DEFAULT NULL,
  'mail' varchar(170) DEFAULT NULL,
  'name' varchar(120) DEFAULT NULL,
  'sex' varchar(20) DEFAULT NULL,
  'vipdate' varchar(25) DEFAULT NULL,
  'vip' tinyint(1) DEFAULT NULL,
  'banned' tinyint(1) DEFAULT NULL,
  'verified' tinyint(1) NOT NULL,
  'image' varchar(100) DEFAULT NULL,
  'online' varchar(11) DEFAULT NULL,
  'time' varchar(25) DEFAULT NULL,
  'language' varchar(120) DEFAULT NULL,
  'country' varchar(255) DEFAULT NULL,
  'day' varchar(11) DEFAULT NULL,
  'month' varchar(11) DEFAULT NULL,
  'year' varchar(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

table of friends

CREATE TABLE 'db_friends' (
  'friend_ID' int(120) NOT NULL,
  'user_one' int(120) NOT NULL,
  'user_two' int(120) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

$SQL_DATA = '? mysqli code';

    $sql = mysqli_query($con,$SQL_DATA) or die ("error en la consulta". mysqli_connect_error());
    if (mysqli_num_rows($sql) == 0)
    {
       echo 'no hay datos';
    }else{
       echo 'josé';
    }

php mysqli db

asked by sode 22.06.2018 в 23:32

source

1 answer

How to show a price according to which items of several selects are selected with Javascript? Show Activity within another Activity

score 1 · Answer 1

Going back to the idea and adapting it to an example, I propose the following solution

One solution is that you have two entities User and friends to name them in some way

It will be a one-to-many relationship; where a user can have many friends; therefore you must have created the user table first and then the friends table

CREATE TABLE usuarios(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) UNIQUE NOT NULL,
    username VARCHAR(100) UNIQUE NOT NULL,
    status_user TINYINT(1) NOT NULL)ENGINE=InnoDB;

Later you can create a table called friends that will be linked to the first one by a foreign key that makes union with the id of the users table, so look

CREATE TABLE amigos(
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    numero VARCHAR(100) UNIQUE NOT NULL,
    name VARCHAR(100) UNIQUE NOT NULL,
    username VARCHAR(100) UNIQUE NOT NULL,
    status_amigo TINYINT(1) NOT NULL,
    user_id BIGINT NOT NULL,
    CONSTRAINT fk_amigos_usuarios FOREIGN KEY(user_id) REFERENCES 
    usuarios(id) ON DELETE CASCADE ON UPDATE CASCADE
    )ENGINE=InnoDB;

Explanation, the user id is saved in the friends table so that so with an INNER JOIN you can get a list of all the friends that has a user

Finally your JOIN could look like this:

SELECT amigos.numero, amigos.name, usuarios.name
    FROM usuarios
    JOIN amigos
    ON usuarios.id = amigos.user_id
    WHERE usuarios.id = 1;

With the previous JOIN at the end I add the WHERE to ask him to just bring me the user's friends with id 3

UPDATE

In your db_users table, you should only place the foreign key that will link both tables so that each time you save a friend you should also save the id of the user with whom this linked; that is, with which he is a friend