CODEWITHSUNDEEP
phpmysqljson
SnareChops
SnareChops

Reputation: 13347

Save array style data to mysql database (JSON)

I have a website that uses a voting feature for some of it's content. I have a MySQL database that stores all of the content entries and each user has an INT id when they are logged in. How do I go about storing a variable amount of data in the database so that I can check if the particular user has voted already to prevent double voting. I have tried this and I'm not sure that it's working quite right.

When a user submits the vote form the existing string saved in the database is sent with the form via post $_POST['voted'] Then I need to add the ID of the person that just voted to the json string and save that back to the mysql database. $_SESSION['ID'] holds the user-id and the field type for Voted in mysql is TEXT

$json = array();
$json = var_dump(json_decode($_POST['voted']));
$json[] = $_SESSION['ID'];
$Voted = json_encode($json);

Upvotes: 0

Views: 963

Answers (2)

Herr Nentu'
Herr Nentu'

Reputation: 1506

You decode your json to an array

$json = json_decode($your_data_from_mysql_query, true); //When TRUE, returned objects will be converted into associative arrays.

then you check if the user has already his id inside the array with this function http://php.net/manual/en/function.in-array.php and if it's not there then you populate the array and go on with your workflow.

Upvotes: 1

user1032531
user1032531

Reputation: 26281

Don't use sessions. Instead, add a new table which joins users and vote-topics. When ever a user votes on a topic, add the user id and topic id to this table. Before allowing a user to vote, check this table.

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL,ALLOW_INVALID_DATES';

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;
USE `mydb` ;

-- -----------------------------------------------------
-- Table `mydb`.`users`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`users` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`vote_topics`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`vote_topics` (
  `id` INT NOT NULL AUTO_INCREMENT ,
  `name` VARCHAR(45) NULL ,
  PRIMARY KEY (`id`) )
ENGINE = InnoDB;


-- -----------------------------------------------------
-- Table `mydb`.`users_has_vote_topics`
-- -----------------------------------------------------
CREATE  TABLE IF NOT EXISTS `mydb`.`users_has_vote_topics` (
  `users_id` INT NOT NULL ,
  `vote_topics_id` INT NOT NULL ,
  PRIMARY KEY (`users_id`, `vote_topics_id`) ,
  INDEX `fk_users_has_vote_topics_vote_topics1_idx` (`vote_topics_id` ASC) ,
  INDEX `fk_users_has_vote_topics_users_idx` (`users_id` ASC) ,
  CONSTRAINT `fk_users_has_vote_topics_users`
    FOREIGN KEY (`users_id` )
    REFERENCES `mydb`.`users` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION,
  CONSTRAINT `fk_users_has_vote_topics_vote_topics1`
    FOREIGN KEY (`vote_topics_id` )
    REFERENCES `mydb`.`vote_topics` (`id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB;



SET SQL_MODE=@OLD_SQL_MODE;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

Upvotes: 0

Related Questions