Adaugare orar zilele saptamanii in baza de date

Discutii despre script-uri si coduri PHP-MySQL, precum si lucru cu XML in PHP.
dim
Mesaje:61

Adaugare orar zilele saptamanii in baza de date

Salut
Am o baza de date cu toate locatiile

Cod: Selectaţi tot

location_id  | name |address |telephone | open

Si un formular prin care incarc datele in bd. Intrebarea mea este daca pot sa inlocuiest acesta linie in care scriu manual programul (Ex: Luni-vineri : 9:00-18:00 Sambata: 9:00 - 14:00 Duminica : Inchis)

Cod: Selectaţi tot

<textarea name="open" rows="5" placeholder="{{ entry_open }}" id="input-open" class="form-control">{{ open }}</textarea>
intr-un formular cu tote zilele saptamani si butone select pt ora deschideri si ora inchideri

Cod: Selectaţi tot

<table class="table table-bordered table-hover">
  <thead>
      <tr>
          <td style="width: 1px;" class="text-center"><input type="checkbox" id="days_check" onclick="$('input[name*=\'days\']').prop('checked', this.checked);"></td>
          <td style="text-align: left">Zi</td>
          <td style="text-align: left">Deschis</td>
          <td style="text-align: left">Inchis</td>
      </tr>
  </thead>
  <tbody>
      <tr>
          <td>
              <input type="checkbox" name="days[]" id="monday_check" value="1" checked="">
          </td>
          <td>
              <label class="col-sm-2 control-label" for="input-status">Luni</label>
          </td>
          <td>
              <select name="monday_time_from" id="monday_time_from" class="form-control">
                                                              <option value="0" selected="">Inchis</option>
                                                              <option value="10">04:30 AM</option>
                                                              <option value="11">05:00 AM</option>
              </select>
          </td>
          <td>
              <select name="monday_time_to" id="monday_time_to" class="form-control">
                  <option value="0" selected="">Inchis</option>
                  <option value="41">08:00 PM</option>
                  <option value="42">08:30 PM</option>
              </select>
          </td>
      </tr>
      <tr>
          <td>
              <input type="checkbox" name="days[]" id="tuesday_check" value="2" checked="">
          </td>
          <td>
              <label class="col-sm-2 control-label" for="input-status">Marti</label>
          </td>
          <td>
              <select name="tuesday_time_from" id="tuesday_time_from" class="form-control">
                  <option value="0" selected="">Inchis</option>
                                                              <option value="10">08:30 AM</option>
              </select>
          </td>
          <td>
              <select name="tuesday_time_to" id="tuesday_time_to" class="form-control">
                  <option value="0" selected="">Inchis</option>

                  <option value="37">06:00 PM</option>
                  <option value="38">06:30 PM</option>
              </select>
          </td>
      </tr>
      <tr>
          <td>
              <input type="checkbox" name="days[]" id="wednesday_check" value="3" checked="">
          </td>
          
          <td>
              <label class="col-sm-2 control-label" for="input-status">Sambata</label>
          </td>
          <td>
              <select name="saturday_time_from" id="saturday_time_from" class="form-control">
                <option value="0" selected="">Inchis</option>
                                                              <option value="10">04:30 AM</option>
                                                              <option value="11">05:00 AM</option>
              </select>
          </td>
          <td>
              <select name="saturday_time_to" id="saturday_time_to" class="form-control">
                   <option value="0" selected="">Inchis</option>
                  <option value="33">04:00 PM</option>
                  <option value="34">04:30 PM</option>
              </select>
          </td>
      </tr>
      <tr>
          <td>
              <input type="checkbox" name="days[]" id="sunday_check" value="7" checked="">
          </td>
          <td>
              <label class="col-sm-2 control-label" for="input-status">Duminica</label>
          </td>
          <td>
              <select name="sunday_time_from" id="sunday_time_from" class="form-control">
                 <option value="0" selected="">Inchis</option>
                                                              <option value="10">04:30 AM</option>
                                                              <option value="11">05:00 AM</option>
                                                              <option value="12">05:30 AM</option>
              </select>
          </td>
          <td>
              <select name="sunday_time_to" id="sunday_time_to" class="form-control">
                   <option value="0" selected="">Inchis</option>
                  <option value="44">09:30 PM</option>
                  <option value="45">10:00 PM</option>
                  <option value="46">10:30 PM</option>
              </select>
          </td>
      </tr>
  </tbody>
</table>
Fara sa modific rubricile din bd
Multumesc
MarPlo Mesaje:4343
Ar fi doua variante:
1. Trimiti datele bifate din tabelul cu programul zilelor, apoi pe partea de server le aranjezi cum erau preluate din acel <textarea name="open" ..>.
Sau
2. Ai putea sa faci un script javascript care sa adauge in acel <textarea name="open" ..> datele bifate in tabelul cu programul zilelor, apoi transmiti acel textarea cu ce a adaugat scriptul js in el.

- Dar depinde cum e codul care preia datele din formular si le adauga in bd.
dim Mesaje:61
Salut.
Cred ca asta intrebai cum e codul care preia datele din formular si le adauga in bd.

Cod: Selectaţi tot

public function addLocation($data) {
		$this->db->query("INSERT INTO " . DB_PREFIX . "location SET name = '" . $this->db->escape($data['name']) . "', address = '" . $this->db->escape($data['address']) . "',  telephone = '" . $this->db->escape($data['telephone']) . "',  open = '" . $this->db->escape($data['open']) . "'");
	
		return $this->db->getLastId();
	}

Cod: Selectaţi tot

if (isset($this->request->post['open'])) {
			$data['open'] = $this->request->post['open'];
		} elseif (!empty($location_info)) {
			$data['open'] = $location_info['open'];
		} else {
			$data['open'] = '';
		}

Ai vrun model ceva asemanato din care as putea sa invat principiu prin care adaug mai multe informatii din mai multe rubrici intr-o singura rubrica din baza de date si dupa acele date sa le afisez separat ?


de ex pe mine m-ar interesa ca ultim ora din program in functie de zi sa imi afiseze magazin deschis pana la ora 18 luni pana vineri si pana la 14 sambata dupa care sa afiseze inchis
MarPlo Mesaje:4343
Poti sa adaugi mai multe informatii, structura de date, intr-o singura rubrica, in format json.
De exemplu poti adauga asa intr-o singura rubrica din bd:

Cod: Selectaţi tot

{
"luni": {"op":"9:00", "cl":"18:00"}
"marti": {"op":"9:00", "cl":"18:00"}
"sambata": {"op":"9:00", "cl":"14:00"}
"duminica": {"op":"inchis"}
}
Apoi, extragi acel json din coloana din tabelul bazei de date, in php transformi formatul json in array si prelucrezi datele din arraY.

De exemplu:

Cod: Selectaţi tot

$ar_open =['duminica'=>['op'=>'inchis'];  //va contine zilele cu programul

// preiei daatele din formular si le adaugi in array
if(isset($_POST['monday_time_from']) && isset($_POST['monday_time_to'])){
  $ar_open['luni'] =['op'=>$_POST['monday_time_from'], 'cl'=>$_POST['monday_time_to']];
}
if(isset($_POST['tuesday_time_from']) && isset($_POST['tuesday_time_to'])){
  $ar_open['marti'] =['op'=>$_POST['tuesday_time_from'], 'cl'=>$_POST['tuesday_time_to']];
}
//..Restul zilelor..

//Transformi array-ul in json
$data['open'] = json_encode($ar_open);

//Adaugi $data in db ca si inainte

//La extragere date din bd, coloana 'open' va contine array-ul cu zilele in format json
//Dupa ce selectezi datele din bd, json-ul din 'open' il transformi in array
// $db_results inlocuiesti cu ce ai tu
$data_open = json_decode($db_results['open'], true); 

//Apoi procesezi datele din $data_open ca orice array, va avea structura $ar_open dinainte de json
Tot codul e mai complicat, verifici cheile 'op' si 'cl' din array de la ziua respectiva si le compari cu ora curenta.
dim Mesaje:61
Am inlocuit

Cod: Selectaţi tot

if (isset($this->request->post['open'])) {
			$data['open'] = $this->request->post['open'];
		} elseif (!empty($location_info)) {
			$data['open'] = $location_info['open'];
		} else {
			$data['open'] = '';
		}
cu

Cod: Selectaţi tot

        $ar_open =['luni'=>['op'=>'inchis'],
		           'marti'=>['op'=>'inchis'], 
		            'miercuri'=>['op'=>'inchis'],
		            'joi'=>['op'=>'inchis'],
		            'vineri'=>['op'=>'inchis'],
			     'sambata'=>['op'=>'inchis'],
			     'duminica'=>['op'=>'inchis']
	             ];


		if(isset($_POST['monday_time_from']) && isset($_POST['monday_time_to'])){
			$ar_open['luni'] =['op'=>$_POST['monday_time_from'], 'cl'=>$_POST['monday_time_to']];
		  }

		if(isset($_POST['tuesday_time_from']) && isset($_POST['tuesday_time_to'])){
			$ar_open['marti'] =['op'=>$_POST['tuesday_time_from'], 'cl'=>$_POST['tuesday_time_to']];
		  }

		if(isset($_POST['monday_time_from']) && isset($_POST['monday_time_to'])){
			$ar_open['miercuri'] =['op'=>$_POST['monday_time_from'], 'cl'=>$_POST['monday_time_to']];
		  }
		  
		if(isset($_POST['tuesday_time_from']) && isset($_POST['tuesday_time_to'])){
			$ar_open['joi'] =['op'=>$_POST['tuesday_time_from'], 'cl'=>$_POST['tuesday_time_to']];
		  } 
		
		  if(isset($_POST['tuesday_time_from']) && isset($_POST['tuesday_time_to'])){
			$ar_open['vineri'] =['op'=>$_POST['tuesday_time_from'], 'cl'=>$_POST['tuesday_time_to']];
		  }

		if(isset($_POST['monday_time_from']) && isset($_POST['monday_time_to'])){
			$ar_open['sambata'] =['op'=>$_POST['monday_time_from'], 'cl'=>$_POST['monday_time_to']];
		  }
		  
		if(isset($_POST['tuesday_time_from']) && isset($_POST['tuesday_time_to'])){
			$ar_open['duminica'] =['op'=>$_POST['tuesday_time_from'], 'cl'=>$_POST['tuesday_time_to']];
		  } 

		  $data['open'] = json_encode($ar_open);
dar nu imi adauga nimic in baza de data.
Ar mai trebui sa faca ceva ?
MarPlo Mesaje:4343
Coloana 'open' din tabel trebuie sa permita siruri mari de text, de exemplu sa fie de tip VARCHAR(600).
Am dat exemplu cu $data['open'] fiindca am presupus ca transmiti la metoda addLocation() variabila $data.
$ar_open transformat in json trebuie pus in datele array pe care le transmiti la addLocation(), la indexul 'open'.
Trebuie sa vezi daca e vreo eroare la comanda sql catre baza de date.
Poti sa verifici cu un 'echo' sirul sql cu datele transmise la bd, sa vezi daca sunt corecte.

Cod: Selectaţi tot

public function addLocation($data) {
  $sql ="INSERT INTO ". DB_PREFIX ."location SET name ='". $this->db->escape($data['name']) ."', address ='". $this->db->escape($data['address']) ."',  telephone ='". $this->db->escape($data['telephone']) ."', open ='". $data['open'] ."'";
  $this->db->query($sql);
  echo $sql;  //pt. verificare, $data['open'] trebuie sa contina json-ul cu orarul zilelor
  return $this->db->getLastId();
}
dim Mesaje:61
Coloana 'open' din tabel erea setata ca tip text, am modificato de tip VARCHAR(600).
si imi intorce
Undefined array key "open"
dim Mesaje:61
Coloana 'open' din tabel erea setata ca tip text, am modificato de tip VARCHAR(600).
si imi intorce
Undefined array key "open"
eraora asta apre daca sterg rubrica

Cod: Selectaţi tot

<textarea name="open" rows="5" placeholder="{{ entry_open }}" id="input-open" class="form-control">{{ open }}</textarea>
din formular
daca il las acolo imi completeaza automat ce am pus in $ar_open si afiseza asta

Cod: Selectaţi tot

{"luni":{"op":"09:00","cl":"18:00"},"marti":{"op":"09:00","cl":"18:00"},"miercuri":{"op":"09:00","cl":"18:00"},"joi":{"op":"09:00","cl":"18:00"},"vineri":{"op":"09:00","cl":"18:00"},"sambata":{"op":"09:00","cl":"15:00"},"duminica":{"op":"inchis"}}

si imi aduga in baza de date ceva de genu asta
{&quot;luni&quot;{&quot;op&quot;:&quot;09:00&quot;,&quot;cl&quot;:&quot;18:00&quot;},&quot;marti&quot;:{&quot;op&quot;:&quot;09:00&quot;,&quot;cl&quot;:&quot;18:00&quot;},&quot;miercuri&quot;:{&quot;op&quot;:&quot;09:00&quot;,&quot;cl&quot;:&quot;18:00&quot;},&quot;joi&quot;:{&quot;op&quot;:&quot;09:00&quot;,&quot;cl&quot;:&quot;18:00&quot;},&quot;vineri&quot;:{&quot;op&quot;:&quot;09:00&quot;,&quot;cl&quot;:&quot;18:00&quot;},&quot;sambata&quot;:{&quot;op&quot;:&quot;09:00&quot;,&quot;cl&quot;:&quot;15:00&quot;},&quot;duminica&quot;:{&quot;op&quot;:&quot;inchis&quot;}}
MarPlo Mesaje:4343
Daca nu mai iti trebue textarea 'open' sterge codul din php care preia acea zona de formular.
E bine ca iti adauga json-ul in tabel, problema e ca in loc de ghilimele duble adauga &quot;, asta presupun ca e de la aplicarea $this->db->escape($data['open']).
Ca sa adauge json-ul corect, incearca sa adaugi direct $data['open'], iar daca nu merge asa, aplica doar addslashes($data['open']).
dim Mesaje:61
Daca sterg textarea 'open' nu imi mai adauga nimic in baza de date
Daca il las imi preia ce apre in el ce este trecut in $ar_open

Cod: Selectaţi tot

$ar_open =['luni'=>['op'=>'09:00', 'cl'=> '18:00'],
		           'marti'=>['op'=>'09:00', 'cl'=> '18:00'], 
				   'miercuri'=>['op'=>'09:00', 'cl'=> '18:00'],
				   'joi'=>['op'=>'09:00', 'cl'=> '18:00'],
				   'vineri'=>['op'=>'09:00', 'cl'=> '18:00'],
				   'sambata'=>['op'=>'09:00', 'cl'=> '15:00'],
				   'duminica'=>['op'=>'inchis']
	             ];
si in bd adauga valorile de aici chiar daca in tabel sunt modificate.

daca il las indiferent de varinta de adaugare sugerata de tine gilimememe le adauga in bd ca &quot;

nu ai nici un tutorial nimic din care as putea sa invat si sa inteleg cum adaug json in baza de date si cum il extrad gupa de acaolo
MarPlo Mesaje:4343
Treaba cu adaugare json in mysql e simpla, se rezuma la adaugarea unui sir care contine ghilimele duble.
Problema la tine e ca acele ghilimele sunt adaugate ca &quot;. Asa transforma scriptul pe care-l folosesti, probabil undeva in cod aplica htmlentities() sau htmlspecialchars() la ce se adauga in mysql.

Testeaza si studiaza acest exemplu de adaugare si preluare sir json in mysql (modifici datele de conectare):

Cod: Selectaţi tot

<?php
// conectare la MySQL 
$conn = new mysqli('localhost', 'root', 'pass', 'test');

// verifica conexiunea
if (mysqli_connect_errno()) {
 exit('Connect failed: '. mysqli_connect_error());
}

//Creaza tabelul
$sql ="CREATE TABLE tb_tst (
 id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
 name VARCHAR(35) NOT NULL,
 jstr VARCHAR(600) NOT NULL
 ) CHARACTER SET utf8 COLLATE utf8_general_ci"; 

// Executa interogarea $sql pentru a crea tabelul
if ($conn->query($sql) === TRUE) echo '<br>Table successfully created';
else echo '<br>Error: '. $conn->error;


//Array pentru adaugat in bd
$arr =['k1'=>['a1'=>'val,:57', 'b2'=>'For json']];
$jstr =json_encode($arr);  //il transformat in sir json

//Adauga sirul json in bd
$sql = "INSERT INTO tb_tst (name, jstr) VALUES ('MarPlo', '". $jstr ."')"; 

// Trimite interogarea $sql la MySQL
if ($conn->query($sql) === TRUE) echo '<br>Datele au fost adaugate';
else echo '<br>Error: '. $conn->error;

//Select one row
$sql = "SELECT id, name, jstr FROM tb_tst WHERE id=1";
$resql = $conn->query($sql);

// daca $resql contine cel putin un rand
if ($resql->num_rows > 0) {
  $ar2 =[];
  while($row = $resql->fetch_assoc()) {
    $jst2 = $row['jstr'];  //preia sirul json
    $ar2 = json_decode($jst2, true);  //il transforma in array
  }

  // afiseaza valoarea de la cheia b2 din k1
  if(isset($ar2['k1']['b2'])) echo '<br>From Select: '. $ar2['k1']['b2'];
}
else echo '<br>0 rezultate';
Acest cod va afisa:

Cod: Selectaţi tot

Table successfully created
Datele au fost adaugate
From Select: For json