Jump to content

Search the Community

Showing results for tags 'mysql'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Welcome to PHP Freaks
    • Announcements
    • Introductions
  • PHP Coding
    • PHP Coding Help
    • Regex Help
    • Third Party Scripts
    • FAQ/Code Snippet Repository
  • SQL / Database
    • MySQL Help
    • PostgreSQL
    • Microsoft SQL - MSSQL
    • Other RDBMS and SQL dialects
  • Client Side
    • HTML Help
    • CSS Help
    • Javascript Help
    • Other
  • Applications and Frameworks
    • Applications
    • Frameworks
    • Other Libraries
  • Web Server Administration
    • PHP Installation and Configuration
    • Linux
    • Apache HTTP Server
    • Microsoft IIS
    • Other Web Server Software
  • Other
    • Application Design
    • Other Programming Languages
    • Editor Help (Dreamweaver, Zend, etc)
    • Website Critique
    • Beta Test Your Stuff!
  • Freelance, Contracts, Employment, etc.
    • Services Offered
    • Job Offerings
  • General Discussion
    • PHPFreaks.com Website Feedback
    • Miscellaneous

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Age


Donation Link

  1. Ok - I have a working query to which I wanted to add one more item from a new table. Thought it was easy but I must be having a bad day. I will post the original working query (now commented out) and the new query with the added item 'driver_num': /* $q = "select a.race_winner, w.wins, substr(a.race_winner, instr(a.race_winner,' ')+1) as last_name, a.race_name, date_format(a.race_date, '%m/%d') as race_date from trk_races a left outer join (select race_winner, count(race_date) as wins from trk_races where race_winner > '' and Season='$selyr' group by race_winner) w on w.race_winner = a.race_winner where a.race_winner > '' and a.Season='$selyr' order by $orderby"; */ $q = "select a.race_winner, w.wins, substr(a.race_winner, instr(a.race_winner,' ')+1) as last_name, a.race_name, date_format(a.race_date, '%m/%d') as race_date, d.driver_num from trk_races a, drivers d left outer join (select race_winner, count(race_date) as wins from trk_races where race_winner > '' and Season='$selyr' group by race_winner) w on w.race_winner = a.race_winner where a.race_winner > '' and a.Season = '$selyr' and d.driver_season = a.Season and a.race_winner = d.driver_name order by $orderby"; The only addition is the "drivers d" in the from clause of the first select along with the additions to the ending where clause that connects the "drivers" table to the "trk_races" table in that select. Here is the error I am getting from this change: Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.race_winner' in 'on clause' in /home/albany/public_html/homejg/nascar/nas_show_drivers.php:129 Stack trace: #0 /home/albany/public_html/homejg/nascar/nas_show_drivers.php(129): PDO->query('select a.race_w...') ..... The error column is already being used elsewhere in the query so I can't figure out why I am getting this error.
  2. For the life of me I can not figure out the syntax error. I was thinking it was related to DateTime field. The data comes over as a string from the api, but converting to time doesn't work. The field in the database is DateTime. Any suggestions would be appreciated. The data from the api for reference. array(1) { [0]=> array(3) { ["macAddress"]=> string(17) "FA:F5:C2:98:6F:29" ["lastData"]=> array(27) { ["dateutc"]=> int(1642622880000) ["tempinf"]=> float(77.9) ["humidityin"]=> int(38) ["baromrelin"]=> float(29.956) ["baromabsin"]=> float(29.132) ["tempf"]=> float(77.2) ["battout"]=> int(1) ["humidity"]=> int(47) ["winddir"]=> int(247) ["windspeedmph"]=> float(5.6) ["windgustmph"]=> float(8.1) ["maxdailygust"]=> float(15.9) ["hourlyrainin"]=> int(0) ["eventrainin"]=> int(0) ["dailyrainin"]=> int(0) ["weeklyrainin"]=> int(0) ["monthlyrainin"]=> float(0.52) ["totalrainin"]=> float(48.862) ["solarradiation"]=> float(196.47) ["uv"]=> int(1) ["feelsLike"]=> float(76.83) ["dewPoint"]=> float(55.39) ["feelsLikein"]=> float(77.2) ["dewPointin"]=> float(50.2) ["lastRain"]=> string(24) "2022-01-12T02:50:00.000Z" ["tz"]=> string(15) "America/Chicago" ["date"]=> string(24) "2022-01-19T20:08:00.000Z" } ["info"]=> array(2) { ["name"]=> string(18) "My Weather Station" ["coords"]=> array(5) { ["coords"]=> array(2) { ["lon"]=> float(-197.65635809999999) ["lat"]=> float(38.6587316) } ["address"]=> string(44) "100 Main Street, Anytown, FL 08226, USA" ["location"]=> string(10) "Anytown" ["elevation"]=> float(214.7066497802734) ["geo"]=> array(2) { ["type"]=> string(5) "Point" ["coordinates"]=> array(2) { [0]=> float(-97.65635809999999) [1]=> float(30.6587316) } } } } } } I take the data from the array and assign it to variables. Notice the unsuccessful attempts to convert string to DateTime. <?php $_DateTime = $data[0]['lastData']['date']; //$_OldDateTime = $data[0]['lastData']['date']; //$_dateTime = strtotime($_OldDateTime); $_tempf = $data[0]['lastData']['tempf']; $_feelsLike = $data[0]['lastData']['feelsLike']; $_stationbarometer = $data[0]['lastData']['baromrelin']; $_sealevelbarometer = $data[0]['lastData']['baromabsin']; $_dewpoint = $data[0]['lastData']['dewPoint']; $_humidity = $data[0]['lastData']['humidity']; $_winddir = $data[0]['lastData']['winddir']; $_windspeed = $data[0]['lastData']['windspeedmph']; $_gust = $data[0]['lastData']['windgustmph']; $_maxdailygust = $data[0]['lastData']['maxdailygust']; $_hourlyrainrate = $data[0]['lastData']['hourlyrainin']; $_dailyrain = $data[0]['lastData']['dailyrainin']; $_weeklyrain = $data[0]['lastData']['weeklyrainin']; $_monthlyrain = $data[0]['lastData']['monthlyrainin']; $_totalrain = $data[0]['lastData']['totalrainin']; $_lastRain = $data[0]['lastData']['lastRain']; //$_OldlastRain = $data[0]['lastData']['lastRain']; //$_lastRain = strtotime($_OldlastRain); $_solar = $data[0]['lastData']['solarradiation']; $_battout = $data[0]['lastData']['battout']; // this is where we insert into the database $sql = "INSERT INTO weather_data (datetime, tempf, feelslike, stationbarometer, sealevelbarometer, dewpoint, humidity, winddir, windspeed, gust, maxdailygust, hourlyrainrate, dailyrain, weeklyrain, monthlyrain, totalrain, lastrain, solar, battout) VALUES ( $_DateTime, $_tempf, $_feelsLike, $_stationbarometer, $_sealevelbarometer, $_dewpoint, $_humidity, $_winddir, $_windspeed, $_gust, $_maxdailygust, $_hourlyrainrate, $_dailyrain, $_weeklyrain, $_monthlyrain, $_totalrain, $_lastRain, $_solar, $_battout )"; //mysqli_query($conn, $sql); if (mysqli_query($conn, $sql)) { echo "Success!"; //json_encode(array("statusCode"=>200)); } else { echo "Error: " . $sql . "<br>" . mysqli_error($conn); } mysqli_close($conn); ?> Error message: Error: INSERT INTO weather_data (datetime, tempf, feelslike, stationbarometer, sealevelbarometer, dewpoint, humidity, winddir, windspeed, gust, maxdailygust, hourlyrainrate, dailyrain, weeklyrain, monthlyrain, totalrain, lastrain, solar, battout) VALUES ( 2022-01-24T15:40:00.000Z, 47.7, 47.7, 30.048, 29.224, 45.49, 92, 314, 1.3, 2.2, 8.1, 0, 0.161, 0.161, 0.681, 49.024, 2022-01-24T15:34:00.000Z, 29.48, 1 ) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':40:00.000Z, 47.7, 47.7, 30.048, 29.224, 45.49, 92, 314, ' at line 6
  3. I have created a form to edit fields in a database table. This is basically what it does sale_stamp : <input type="text" name="sale_stamp" value="<?php echo $sale_stamp;?>" > $sale_stamp = $_POST['sale_stamp']; $sql = "UPDATE mytable SET sale_stamp = :sale_stamp WHERE id = :id"; $stmt=$db->prepare($sql); $stmt->execute(array( ':id' => $id, ':sale_stamp' => $sale_stamp); This works provided there are values entered but some fields may be blank and this fails for numeric fields. The generated sql is UPDATE asset set sale_stamp = '' WHERE id = '17' The table definition allows null, but when I leave the field blank in the table the generated sql has an empty string rather than null. How do I overcome this?
  4. In my DB schema, there are 4 tables and its relationship as shown in the below attached image. My question is how many schools are there according to the data in the school table and how many of those schools belong to provinces, districts and zones, is it possible in one mysql query? Currently I am using 4 separate queries for this and the relevant code is as follows. $sql = "SELECT count(school_id) as schtot FROM sn_school"; $stmt = $pdo->query($sql); $schtot = $stmt->fetchColumn(); $schtot = str_pad($schtot , 3, 0, STR_PAD_LEFT); $sql = "SELECT count(zone_id) as zonetot FROM sn_school GROUP BY zone_id"; $stmt = $pdo->query($sql); $zonetot = $stmt->rowCount(); $zonetot = str_pad($zonetot , 3, 0, STR_PAD_LEFT); $sql = "SELECT COUNT(d.district_id) as districtTot FROM sn_school s JOIN zone z USING(zone_id) JOIN district d ON d.district_id = z.district_id GROUP BY d.district_id"; $stmt = $pdo->query($sql); $districtTot = $stmt->rowCount(); $districtTot = str_pad($districtTot , 3, 0, STR_PAD_LEFT); $sql = "SELECT COUNT(p.province_id) as ptot FROM sn_school s JOIN zone z USING(zone_id) JOIN district d ON d.district_id = z.district_id JOIN district p ON p.province_id = d.province_id GROUP BY p.province_id"; $stmt = $pdo->query($sql); $ptot = $stmt->rowCount(); $ptot = str_pad($ptot , 3, 0, STR_PAD_LEFT);
  5. I have a query I'm using in my CodeIgniter model to fetch the count of listings of products between particular days. What this query is doing is it's taking status_from from logs where the date is during and after the selected date range and taking added_date from listings where the date falls before the from date range picked by the user and calculates it. Once it has retrieved those records, it checks the table for what variable that status holds and does a sum(case when else 0) to get the total count. function get_report(){ $sql = with Y as ( with recursive D (n, day) as ( select 1 as n, '2021-09-25' my_date union select n+1, day + interval 1 day from D where day + interval 1 day < '2021-10-15' ) select * from D ), X as ( select Y.day, l.*, (select status_from from logs where logs.refno = l.refno and logs.logtime >= Y.day order by logs.logtime limit 1) logstat from listings l, Y where l.added_date <= Y.day ), Z as ( select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt from X group by X.day, stat_day ) select Z.day, sum(case when Z.stat_day = 'D' then Z.cnt else 0 end ) Draft, sum(case when Z.stat_day = 'A' then Z.cnt else 0 end ) Action, sum(case when Z.stat_day = 'Y' then Z.cnt else 0 end ) Publish, sum(case when Z.stat_day = 'S' then Z.cnt else 0 end ) Sold, sum(case when Z.stat_day = 'L' then Z.cnt else 0 end ) Let from Z group by Z.day order by Z.day; $query = $this->db->query($sql); return $query; } Dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6789f0517b194b09d235860dc794ea14 Now here as you can see I'm processing my calculations in the sql statement itself, but I want to do these calculations in my php side, basically iterating everytime it encounters Z.stat_day = 'D' then adding 1 to Draft column and same for the other statuses. Current View Class: <?php foreach($data_total as $row ){ $draft = $row->draft ? $row->draft : 0; $publish = $row->publish ? $row->publish : 0; $action = $row->action ? $row->action : 0; $sold = $row->sold ? $row->sold : 0; $let = $row->let ? $row->let : 0; ?> <tr> <td><?= $row->day?></td> <td><?= $draft ?></td> <td><?= $publish ?></td> <td><?= $action ?></td> <td><?= $sold ?></td> <td><?= $let ?></td> </tr> <?php } ?> Basically I want to refractor my PHP code to do the same thing the Mysql statement is doing, but keeping the query simple and all processing in the PHP side for performance reasons.
  6. Currently I have a table that displays a total count of my data values for each source. I'm getting this value after comparing 2 tables 1 is crm_leads with all my product information and 1 is crm_sources with what sources are the products related to. Now this is the output: Now as you can see the total count is shown under each header next to its source. Now these count values are inside a tags which once clicked go to viewall page. Here basically I want to show the data of the item that I had clicked. So for example, if I clicked the 163 under Hot status, it takes me to the view all page and shows me id, source, enquiry_date for all those under status Hot in a table. So basically it should detect the data for which source and which status is clicked and then accordingly make a statement like this? select * from crm_leads where lead_source = '.$source.' and lead_status = '.$status.'; Model Class: function get_statusreport($fdate='',$tdate='') { $this->db->select("l.lead_status,crm_sources.title,count(*) as leadnum,l.enquiry_date,l.sub_status"); $this->db->from($this->table_name." as l"); if($fdate !='') $this->db->where("date(l.added_date) >=",date('Y-m-d',strtotime($fdate))); if($tdate !='') $this->db->where("date(l.added_date) <=",date('Y-m-d',strtotime($tdate))); $this->db->where("lead_status <>",10); $this->db->join("crm_sources ","crm_sources.id= l.lead_source","left"); $this->db->group_by("l.lead_status,crm_sources.title"); $this->db->order_by("leadnum DESC, crm_sources.title ASC,l.lead_status ASC"); $query = $this->db->get(); $results = $query->result_array(); return $results; } Controller Class(leadstatus holds the view for my current table): public function leadstatus($slug='') { $content=''; $content['groupedleads'] = $this->leads_model->get_statusreport($fdate,$tdate); $this->load->view('crm/main',$main); $this->load->view('crm/reports/leadstatus',$content); } public function viewall($slug='') { $content=''; $this->load->view('crm/main',$main); $this->load->view('crm/reports/viewall',$content); } View class: <?php $ls_arr = array(1=>'Open',8=>'Hot',2=>'Closed',3=>'Transacted',4=>'Dead'); foreach($groupedleads as $grplead){ $statuses[] = $status = $ls_arr[$grplead["lead_status"]]; if($grplead["title"] == NULL || $grplead["title"] == '') $grplead["title"] = "Unknown"; if(isset($grplead["title"])) $titles[] = $title = $grplead["title"]; $leaddata[$status][$title] = $grplead["leadnum"]; } if(count($titles) > 0) $titles = array_unique($titles); if(count($statuses) > 0) $statuses = array_unique($statuses); ?> <table> <tr"> <th id="status">Source</th> <?php if(count($statuses) > 0) foreach($statuses as $status){ ?><th id=<?php echo $status; ?>><?php echo $status; ?></th> <?php } ?> <th>Total</th> </tr> <?php if(is_array($titles)) foreach($titles as $title){ ?> <tr> <?php $total = 0; echo "<td>".$title."</td>"; foreach ($statuses as $status) { $num = $leaddata[$status][$title]; echo "<td><a target='_blank' href='".site_url('reports/viewall')."'>".$num."</a></td>"; $total += $num; $sum[$status] += $num; } echo "<td>".$total."</td>"; $grandtotal += $total; ?> </tr> <?php } ?> </table>
  7. Here is the dbfiddle for better understanding, refer this when reading question: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0919cacb5d117450168cdc917433a45e I have 2 tables called listings and logs table. The listings table holds a products reference number and it's current status. So suppose if it's status was Publish currently and it's sold later, the status updates to Sold. Here the refno. in this table is unique since the status can change for 1 product. Now I have another table called Logs table, this table records all the status changes that have happened for a particular product(referenced by refno) in a particular timeframe. The initial entry in the listings table is not recorded here, but once it's status is changed, that entry is shown here. Suppose I have the following Listings table('D' => 'Draft', 'A' => 'Action', 'Y' => 'Publish', 'S' => 'Sold', 'N' => 'Let'): INSERT INTO listings VALUES (3, 'Y','2021-05-02','2021-10-02','LP01'), (4, 'A','2021-05-01','2021-05-01','LP02'), (5, 'S','2020-10-01','2020-10-01','LP03'), (6, 'N','2021-05-01','2021-10-06','LP06'), (10, 'D','2021-10-06','2021-10-06','LP05'), (11, 'D','2021-01-01','2021-01-01','LP04'); Here as of now the total count under every status would give: |status_1|c| |:---:|:--:| |Publish|1| |Action|1| |Sold|1| |Left|1| |Draft|2| But if I wanted only the count for entries made in 2020-10-01 it'll show 0 under all statuses except sold, where it'll show 1. Now in this timeframe between 2020-10-01 and today, there have been values entered in listings table as shown above and also for some, the statuses have changed. Status table: INSERT INTO logs VALUES (1, 'Let','Action','2021-06-01','LP01'), (2, 'Action','Draft','2021-10-01','LP01'), (3, 'Draft','Publish','2021-10-02','LP01'), (4, 'Action','Let','2021-10-06','LP06'); What is being shown right now in my listings table is the values after the status change has been made. So now to get the total count on a particular day, I'm having my statement reference the dates from the logs table and respectively subtract the status_to, and add the status_from. Query for this is in the dbfiddle provided above. Here I made it to return data that happened on or before 2021-10-01 and it does not give the right output. Another problem with this query is I cannot return the data for the entries that had initially taken place. For example like I mentioned above the value for the data on 2020-10-01 should show 1 under sold, while 0 under everything else(desired output), but it does not do this since there are no logs made in logs table for when a new entry in initially added. So basically what I want here is to get the initial entries as well with the same entry. If you want an easier explanation for what I'm trying to achieve, please refer to this:
  8. Currently I have 2 tables, the first table shows a count of statuses, refno. and agent_id(person in charge of the refno.) and the second table has an id and agent_name. So to refer a particular agent next to the refno. in table 1, you can reference it via the id of the agent table. Dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=d0e8222a1e49774bcfbcfa30cec75732 Now I have found out that some of my listings have the agent_id as 0 and null, which doesn't have a reference in my agents table. So here I'm using COALESCE to add an extra row called Unassigned and inserting all variables with agent_id 0 or null inside this column. I've tried this same in my codeigniter model: function get_totalagentstatus(){ $this->db->select("SUM(CASE WHEN t.status = 'D' THEN 1 END) AS draft, SUM(CASE WHEN t.status = 'N' THEN 1 END) AS unpublish, SUM(CASE WHEN t.status = 'Y' THEN 1 END) AS publish, SUM(CASE WHEN t.status = 'U' THEN 1 END) AS action, SUM(CASE WHEN t.status = 'L' THEN 1 END) AS unlisted, SUM(CASE WHEN t.status = 'S' THEN 1 END) AS sold, SUM(CASE WHEN t.status = 'T' THEN 1 END) AS let, COALESCE(c.display_name,'Unassigned'), SUM(t.status = 'D') +SUM(t.status = 'N') + SUM(t.status = 'Y') + SUM(t.status = 'U') + SUM(t.status = 'L' ) + SUM(t.status = 'S' )+ SUM(t.status = 'T' ) AS total, t.agent_id, c.display_name"); $this->db->from('crm_listings t'); $this->db->join('crm_clients_users c','t.agent_id = c.id'); $this->db->where('archive="N"'); $this->db->group_by('COALESCE(c.display_name,"Unassigned")'); $results = $this->db->get(); return $results; } Controller Class: $content['total_agent_status'] = $this->leads_model->get_totalagentstatus()->result(); View Class: <?php foreach($total_agent_status as $row ){ $draft = $row->draft ? $row->draft : 0; $unpublish = $row->unpublish ? $row->unpublish : 0; $publish = $row->publish ? $row->publish : 0; $action = $row->action ? $row->action : 0; $unlisted = $row->unlisted ? $row->unlisted : 0; $sold = $row->sold ? $row->sold : 0; $let = $row->let ? $row->let : 0; $total = $row->total ? $row->total : 0; ?> <tr> <td><?= $row->display_name ?></td> <td><?= $draft ?></td> <td><?= $unpublish ?></td> <td><?= $publish ?></td> <td><?= $action ?></td> <td><?= $unlisted ?></td> <td><?= $sold ?></td> <td><?= $let ?></td> <td><?= $total ?></td> </tr> Now this returns everything except the Unassigned row which I want. I've also input this in my phpmyadmin to see the result and it does not return it there either, instead it shows the output with these headers and Unassigned is not there in any of the entries here:
  9. Currently I'm using CodeIgniter and Mysql to fetch my data. Here I'm using the following model to get a count of each status from my database: function get_total(){ $this->db->select("SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, SUM(CASE WHEN status_to = 'Unlisted' THEN 1 END) AS unlisted, SUM(CASE WHEN status_to = 'Sold' THEN 1 END) AS sold, SUM(CASE WHEN status_to = 'Let' THEN 1 END) AS let"); $this->db->from('crm_logs'); $results = $this->db->get(); return $results; } Then to get this model I've used the following controller class: public function totallistings($slug='') { $fdate = $this->input->post("fdate"); $tdate = $this->input->post("tdate"); if ($fdate) { $content['fdate'] = $fdate; } else { $content['fdate'] = ''; } if ($tdate) { $content['tdate'] = $tdate; } else { $content['tdate'] = ''; } $content['data_total'] = $this->leads_model->get_total()->result(); $main['content']=$this->load->view('crm/reports/totallistings',$content,true); $this->load->view('crm/main',$main); } And this is my View class: <?php if(isset($data_sum) && count($data_sum) > 0) { foreach($data_sum as $row ){ $draft = $row->draft ? $row->draft : 0; $unpublish = $row->unpublish ? $row->unpublish : 0; $publish = $row->publish ? $row->publish : 0; $action = $row->action ? $row->action : 0; $unlisted = $row->unlisted ? $row->unlisted : 0; $sold = $row->sold ? $row->sold : 0; $let = $row->let ? $row->let : 0; ?> <tr> <td><?= $draft ?></td> <td><?= $unpublish ?></td> <td><?= $publish ?></td> <td><?= $action ?></td> <td><?= $unlisted ?></td> <td><?= $sold ?></td> <td><?= $let ?></td> </tr> <?php } } else { ?> <?php } ?> I now found out that there are more status words other than draft, publish, etc in my database. So I wanted a way to make this `<th>` and `<td>` dynamic but still having the same functionality it is currently having.
  10. Currently I'm using CodeIgniter MVC framework and Mysql to retrieve a count of data within a particular timeframe. My table data looks like this: Where there are different agents who are changing statuses in a particular timeframe. So what I want is to layout all the agents in the table and display what status change they had made in a particular timeframe. To do this I used the following model to get all the agents in the DB: function get_agent(){ $this->db->distinct(); $this->db->select("agent_id"); $this->db->from('crm_logs'); return $this->db->get(); } And this model to get a count of the status changes: function get_agentstatus($fdate,$tdate,$agent_id){ $this->db->select("SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, $this->db->from('crm_logs'); $this->db->where('cast(logtime as date) BETWEEN "' . $fdate . '" AND "' . $tdate . '" AND agent_id="'.$agent_id.'"'); $results = $this->db->get(); return $results; } In my controller class I have used the following code to get the models: public function agentlistings($slug='') { $fdate = $this->input->post("fdate"); $content['tdate'] = $tdate = $this->input->post("tdate"); if(isset($fdate)){ $content['fdate'] =$fdate; }else{ $content['fdate'] = ''; } if(isset($tdate)){ $content['tdate'] =$tdate; }else{ $content['tdate'] =''; } $content['agent'] = $this->leads_model->get_agent()->result_array(); $content['agent_status'] = $this->leads_model->get_agentstatus($fdate,$tdate,$content['agent'])->result_array(); $main['content']=$this->load->view('crm/reports/agentlistings',$content,true); $this->load->view('crm/main',$main); } And the following in my View class: <table id="statustbl" class="table-fill"> <thead> <tr style="height:<?php echo $height; ?>;"> <th>Agent</th> <th>Draft</th> <th>Unpublish</th> <th>Publish</th> <th>Action</th> </tr> </thead> <tbody class="table-hover"> <?php foreach ($agent as $row) { echo '<tr><td>' .$row['agent_id']. '</td></tr>';//column names } ?> </tbody> </table> With this code I'm able to fill up all my distinct agents into the agents column, But I do not know how to show the agent_status with thier respective agent_id. I have tried SELECT SUM(CASE WHEN status_to = 'Draft' THEN 1 END) AS draft, SUM(CASE WHEN status_to = 'Unpublish' THEN 1 END) AS unpublish, SUM(CASE WHEN status_to = 'Publish' THEN 1 END) AS publish, SUM(CASE WHEN status_to = 'Action' THEN 1 END) AS action, agent_id FROM 'crm_logs' WHERE cast(logtime as date) BETWEEN "2021-09-25" AND "2021-10-01" GROUP BY agent_id to get the data in the same query, but this did not return the correct output.
  11. I am new in PHP Programming , Please Help me. I have made a data table, then i have connected this table after that I have made a table in my index.php file . but i am facing problem to show data from data table in my index.php file, can you help me to solve this problem? <?php include "db.php"; ?> <!doctype html> <html lang="en"> <head> <!-- Required meta tags --> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <!-- Bootstrap CSS --> <link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous"> <title>Post Global Variable</title> </head> <body> <div class="row p-5"> <table class="table table-dark"> <thead> <tr> <th scope="col">#</th> <th scope="col">Name</th> <th scope="col">User Name</th> <th scope="col">Email Address</th> <th scope="col">Password</th> <th scope="col">Phone</th> <th scope="col">Join Date</th> <th scope="col">Action</th> </tr> </thead> <tbody> <?php $myQuery = "SELECT * FROM users"; $allUsers = mysqli_query ($db, $myQuery); while ($row = mysqli_fetch_assoc($allUsers)){ $id= $row['id']; $name= $row['name']; $userName= $row['userName']; $email= $row['email']; $password= $row['password']; $phone= $row['phone']; $join_date= $row['join_date']; ?> <tr> <th scope="row"><?php echo $id; ?></th> <td><?php echo $name; ?></td> <td><?php echo $userName; ?></td> <td><?php echo $email; ?></td> <td><?php echo $password; ?></td> <td><?php echo $phone; ?></td> <td><?php echo $join_date; ?></td> <td><a class="btn btn-success btn-sm" href="#" role="button">Update</a> <a class="btn btn-danger btn-sm" href="#" role="button">Delete</a> </td> </tr> <?php } ?> </tbody> </table> </div> <!-- Optional JavaScript --> <!-- jQuery first, then Popper.js, then Bootstrap JS --> <script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script> <script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script> <script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script> </body> </html>
  12. wrote a stored procedure this morning and i don’t know how to get the values out of it through a class function in php or phpmyadmin. here is what i wrote : public function totalProcedures($friend_name,$session_id) { /* *query to fetch stored procedure */ try { //executing the stored procedure $sql_sp="CALL timeline (:friend, :session,@updates, @group_posts)"; $stmt_sp= $this->_db->prepare($sql_sp); $stmt_sp->bindValue(":friend",$friend_name); $stmt_sp->bindValue(":session",$session_id); $stmt_sp->execute(); $rows=$stmt_sp->fetch(PDO::FETCH_ASSOC); $stmt_sp->closeCursor(); // closing the stored procedure //trying to get values from OUT parameters. $stmt_sp_2=$this->_db->prepare("select @updates,@group_posts"); $stmt_sp_2->execute(); return $stmt_sp_2->fetch(PDO::FETCH_ASSOC); } catch (PDOException $ei) { echo $ei->getMessage(); } } can someone helpme how to get results. here is the storedprocedure: DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `timeline`(IN `friend` VARCHAR(255), IN `session_id` VARCHAR(255), OUT `updates` VARCHAR(62555), OUT `group_posts` VARCHAR(62555)) BEGIN select * FROM updates where author in (friend,session_id) order by time desc limit 5; select * FROM group_posts where author_gp in (friend,session_id) order by pdate desc limit 5; END$$ DELIMITER ; i get the result in php myadmin as follows: how do i do this inside a php class function. CALL timeline('shan2batman','aboutthecreator', @updates, @group_posts);
  13. So I have a database that is structured like this: https://imgur.com/a/DdyTqiE Sample data: https://imgur.com/a/kYwmuO1 For each appointment, a student can have multiple categories, such as 'Academic Probation, Re-Admit' etc... I would like to loop through this table, and get a count of how many were 'is_no_show' and 'is_cancelled' per (unique) category with respect to 'scheduled_student_services.' For example, in the sample data, we see that the first appointment has 4 categories (Academic Probation, Entering Cohort Year 20051, First Generation, and Re-Admit'). Now one of these categories - Academic Probation matches up with what they were scheduled for - '1st Term Probation Advising'. And they cancelled this appointment, so we want the count of cancellations under Academic Probation to go up by 1. How would I approach this? I know I probably need to do two loops but I'm not sure the PHP syntax for this. Any suggestions or tips would be helpful. Thank you for your time!
  14. Hello Everyone - I am playing around with some MYSQL and PHP project I have. I ran into a complex problem getting a PHP table filled with data from MYSQL. I will try to explain what I am trying to do: I am trying to do something like this but in PHP. This is my data from MYSQL database. The Table is called Children This is a quick explanation of how each column on the first screenshot should be filled from the database. This code is what I have so far... to be honest i am not sure how to get the totals of rest of the columns. Maybe use I can use subqueries or if statements... not sure! Can you please help me out? $r = mysqli_query($dbc,"SELECT Classrooms.ClassroomName, COUNT(*) AS TotalChildren FROM Children JOIN Classrooms ON Children.classroomID = Classrooms.classroomID GROUP BY Classrooms.ClassroomName");
  15. Hi, I have a MySql query which currently looks like this Query = "select Question,Answer1,Answer2,Answer3,CorrectAnswer,id,Duplicate from Questions where id='" + RandomN.Text + "'" As you can see I have a table called 'Questions' which has several columns, Im using VB.NET I also have a textBox on a windows form called 'RandomN' What this does is it takes whatever number is in my textBox called RandomN.Text and looks for that number in the id column and returns all data on that row. What I am trying to acheive now is this, I have a column called Duplicate it will either contain the word 'True' or 'False' I would like it to only return data from the given number in the textBox if the Duplicate column in that row contains the word 'False' If someone can shed some light I would be greatful. Thanks
  16. Hi, I have created a quiz and the questions get read from a MySql database table. I am now wanting to stop it repeating questions, when I first started this I thougt it would be easy just create a column called Duplicate and everytime a question got pulled I would mark it in the databse. So it would only read questions that had a "0" in the DuplicateCol and after reading the question it would put a "1" in place of the "0" That would work, the problem being multiple people are using it so if a 1 is there they wont all get asked the question. Anyway that idea is a fail now, which is a shame because everything else worked. Im hoping someone can show me another way on stopping duplicates. when I created the quiz I created a registration, and login page which have there own table I then created another table for the questions with multiple columns ie " id, Question, Answer1, Answer2, Answer3, CorrectAnswer, DuplicateCol " I had a number on my form which is a random number that lets say is "6" will then pull the question from column id 6 Query = "select Question,Answer1,Answer2,Answer3,CorrectAnswer,DuplicateCol from Questions where DuplicateCol= '0' AND id='" + RandomN.Text + "'" Anyway im for the time being lost until someone can give me ideas please, The registration, login table looks like this id, Serial, Email, UserName, Location, UserScore, Activated, I was thinking of adding q1, q2, q3, q4, q5, etc etc and then somehow if a question got pulled in my questions table with id "6" then put a "1" in the column but I think it might be to hard to try and cross reference 2 tables. Any Ideas ?
  17. I am trying to add a bootstrap class to php echo in mysql query but it doesn't work Here the code that I using $result = $conn->query($sql); echo ""; echo " New Users "; echo " "; echo ""; Any ides ?
  18. I'm a newbie, can you please help? I'm getting a mysql error "Unknown column 'E0000001' in 'where clause'" The id is in the URL: ...user-profile.php?id=E0000001 My Query $query = mysqli_query($con, "SELECT * FROM UserList WHERE UserID=".$id) or die (mysqli_error($con)); Thank you
  19. Data from category table: +-------------+-----------+---------------------+-------------+ | category_id | parent_id | name | description | +-------------+-----------+---------------------+-------------+ | 1 | NULL | Products | NULL | | 2 | 1 | Computers | NULL | | 3 | 2 | Laptops | NULL | | 4 | 2 | Desktop Computers | NULL | | 5 | 2 | Tab PCs | NULL | | 6 | 2 | CRT Monitors | NULL | | 7 | 2 | LCD Monitors | NULL | | 8 | 2 | LED Monitors | NULL | | 9 | 1 | Mobile Phones | NULL | | 10 | 9 | LG Phone | NULL | | 11 | 9 | Anroid Phone | NULL | | 12 | 9 | Windows Mobile | NULL | | 13 | 9 | iPad | NULL | | 14 | 9 | Samsung Galaxy | NULL | | 15 | 1 | Digital Cameras | NULL | | 16 | 1 | Printers and Toners | NULL | | 17 | 14 | Galaxy S Series | NULL | | 18 | 14 | Galaxy Note Series | NULL | | 19 | 14 | Galaxy Z Fold2 5G | NULL | | 20 | 17 | Phone 1 | NULL | | 21 | 17 | Phone 2 | NULL | +-------------+-----------+---------------------+-------------+ Just think, I hava an array of category ids for delete like this: ids = [9,17,20]; Now I want to delete the category related to the above array and update the relevant child category. According to this example, the parent_id of the category_id 10,11,12,13,14 should be 1 The parent_id in category 21 should be updated to 14. In another case, suppose I delete category 9, 18 then all the relevant sub and sub sub categories should be updated as parant category. I hope somebody may help me out. Thank you
  20. Howdy folks, I have decided, after a discussion with Barand, to finally hang up the MySQLi shoes and move over to the dark side of PDO. I am trying to Update a profile, for example, but it is not working. No errors or anything. New to PDO so would love some help on figuring out where I am going wrong. Probably everywhere knowing me lol. Here is the dreaded code: if(isset($_POST['submit'])){ $id = trim($_SESSION['id']); //$trn_date = trim($db, date("Y-m-d H:i:s")); //$password = $db->real_escape_string(md5($_POST['password'])); $image = trim($_FILES['image']['name']); $name = trim($_POST['name']); $phone = trim($_POST['phone']); $email = trim($_POST['email']); $address = trim($_POST['address']); $license_number = trim($_POST['license_number']); $position = trim($_POST['position']); $role = trim($_POST['role']); $submittedby = trim($_SESSION["username"]); // image file directory $target = "images/".basename($image); if(!empty($_FILES['image']['name'])) { $sql = "UPDATE users SET name = :name, email = :email, phone = :phone, address = :address, license_number = :license_number, position = :position, role = :role, submittedby = :submittedby, image = :image"; }else{ $sql = "UPDATE users SET name = :name, email = :email, phone = :phone, address = :address, license_number = :license_number, position = :position, role = :role, submittedby = :submittedby"; } $stmt= $db->prepare($sql); if (move_uploaded_file($_FILES['image']['tmp_name'], $target)) { $msg = "Image uploaded successfully"; }else{ $msg = "Failed to upload image"; } if(!$stmt){ if ($stmt->execute()){ $message = ' <i class="fa fa-check text-danger"> Something went wrong please contact the server admin.</i>'; } else{ $message = ' <i class="fa fa-check text-success"> Record Updated!</i>'; } } } Any help folks would be appreciated
  21. I have some sales data as below: SELECT bv.sale_time , amount_due , round(sum(amount_paid), 2) as paid , m.payment_method_id , m.method , bt.payment_status FROM basket_amount_due bv JOIN basket bt USING(basket_id) LEFT JOIN basket_payment p USING (basket_id) JOIN payment_method m USING(payment_method_id) GROUP BY bv.basket_id; +---------------------+------------+---------+-------------------+-------------------+----------------+ | sale_time | amount_due | paid | payment_method_id | method | payment_status | +---------------------+------------+---------+-------------------+-------------------+----------------+ | 2021-09-18 12:19:04 | 1170.00 | 1170.00 | 1 | CASH | paid | | 2021-09-18 12:19:39 | 756.60 | 0.00 | 1 | CASH | due | | 2021-09-18 12:20:22 | 1115.50 | 1000.00 | 1 | CASH | partial | | 2021-09-18 12:21:47 | 990.00 | 990.00 | 4 | Cash on Delivery | paid | | 2021-09-18 12:23:33 | 698.40 | 0.00 | 4 | Cash on Delivery | due | | 2021-09-18 12:29:45 | 2070.00 | 2070.00 | 2 | Credit/Debit Card | paid | +---------------------+------------+---------+-------------------+-------------------+----------------+ 6 rows in set (0.004 sec) My question is, Now I need to get total sales by payment method and payment status. That mean I want, total cash sales (= cash(paid) + Card (paid) + Cash on Delivery (paid)) total credit sales (= cash(due) + cash(partial) + Cash on Delivery (due)) total cash on delivery sales (= Cash on Delivery (paid) + Cash on Delivery (due)) total card sales This is the query I have so far.. SELECT DATE(bv.sale_time) , CASE p.payment_method_id WHEN (1 AND 2) THEN sum(amount_due) ELSE 0 END AS total_cash_sales , CASE p.payment_method_id WHEN 4 THEN sum(amount_due) ELSE 0 END AS total_credit_sales FROM basket_amount_due bv JOIN basket bt USING(basket_id) LEFT JOIN basket_payment p USING (basket_id) JOIN payment_method m USING(payment_method_id) WHERE DATE(bv.sale_time) >= CURDATE() AND DATE(bv.sale_time) < CURDATE() + INTERVAL 1 DAY GROUP BY bv.sale_time; Hope somebody may help me out to figure this out.
  22. Hi, Phreaks, I'm stuck on something that maybe someone can help with on trying to send a user information to phpmyadmin I get this error -> There's 2 problems here, as you can see. 1) I've got an invalid datetime format. 2) is obviously the incorrect string value on my salt value. here is the corresponding code -> <?php if($validation->passed()) { $user = new User(); $salt = Hash::salt(32); try { $user->create(array( "username" => Input::get("usernane"), "password" => Hash::make(Input::get("password"), $salt), "email" => Input::get("email"), "salt" => $salt, "signup_date" => date_create('now')->format('Y-m-d H:i:s'), "role" => 2, )); $sent = true; } catch(Exception $e) { die($e->getMessage()); } } else { $errors = $validation->errors(); } It can be seen how I did both of them here. Additionally the Hash::salt method is here -> <?php public static function salt($length) { return random_bytes($length); } the column 'signup_date' is in my database as a datetime not null with 'none' as default. The column 'salt' is a VARCHAR(32) not null, 'none' as default Can anyone help me out with what I've done wrong, please. TIA
  23. Hello, I have the following code that should select all strings that start with "rog" (like rogers, etc). SELECT user_id, first_name AS name, gender FROM user_details WHERE MATCH (first_name) AGAINST('rog*') The problme it's that shows me zero results. In first_name column I have a lot of words with "rogers". first_name column have Fulltext assign. If I use just "...AGAINST ('rogers')" it return corect data. It's something wrong with my sentence? Thank you
  24. I have a general question. Just need to be put on the right direction. I made that little game I put on a server. Now I wish that 2 persons could play against each other online. So I made a signup/login and I can list all the users against which you would like to play. Now how could I know who is actually online and send him/her an alert when it is his/her turn to play? My first idea was to send emails every time it's a player turn to make a move; but there must be something more elegant to do in-game? I'm having a lot of fun coding that thing and any help would be great 🙂
  25. Hi, I am making a shopping cart and I have a problem. My code to remove the items from the cart works except for the first added item; I can delete all but that one. This is the form to remove the items. <form class="col-md-12" method="POST" action="" id="formremove"> <input type="hidden" name="id_prod" value="<?php echo $row['id_producto'] ?>"> <input type="submit" name="btn-remove" class="btn btn-info btn-sm" id="remove" value="X"> </form> And this is the php if ($_POST['btn-remove']) { $id_user=$_SESSION['id_user']; $id_prod = $_POST['id_prod']; $SQLREM = "DELETE FROM `carrito` WHERE id_user='$id_user' and id_producto='$id_prod';"; if (mysqli_query($conn, $SQLREM)) { echo '<script> alert("Ok"); window.location="/CBA/WAUW/cart.php"</script>'; } else { echo '<script> alert("Error"); window.location="/CBA/WAUW/cart.php"</script>'; }; } The connection to the database works fine; as I said before, it deletes everything but the first item added. I would be very grateful for any help.
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.