[Solved] how to select photos from another table using mysql,php

by


First of all you have to create connection between these two tables, add column user_id in photos table.

CREATE TABLE
IF NOT EXISTS photos (
    id INT (11) NOT NULL AUTO_INCREMENT,
    location VARCHAR (100) NOT NULL,
    caption VARCHAR (100) NOT NULL,
    user_id int(11) NOT NULL,
    PRIMARY KEY (id)
) ENGINE = MyISAM DEFAULT CHARSET = latin1 AUTO_INCREMENT = 5;

Then change the structure of your users table, you have to store avatar as int with photo id.
Change avatar from varchar to integer value.

CREATE TABLE users (
    id BIGINT (20) NOT NULL,
    username VARCHAR (255) NOT NULL,
    PASSWORD VARCHAR (255) NOT NULL,
    email VARCHAR (255) NOT NULL,
    age BIGINT (200) NOT NULL,
    gender VARCHAR (10) NOT NULL,
    avatar INT(10) NOT NULL,
    signup_date INT (10) NOT NULL
    ) ENGINE = MyISAM DEFAULT CHARSET = utf8;

Then when user will add data into photos table just write user_id and then you will have ability to get all users photos with MySQL query:

SELECT * FROM `photos` WHERE user_id={your_user_id}

Then you will choose id of one photo that you want to make avatar and will update users table column named avatar with photo id (photos.id)

7

solved how to select photos from another table using mysql,php