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. 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
  2. I am having trouble adding sub-topics to my home made blog system running under PHP-Fusion CMS with PHPver7.4.16 with cgi/fcgi interface and MySQL5.7.34-log. Here are 2 images: Here is the module that produces the output. <?php echo "<div class='col-sm-12'>\n"; echo "<table width='100%' border='0'><tr><td><span class='hdspan2'><b>".$locale['gb_810']."</b></span></td></tr></table>\n"; echo "<table align='center' width='80%' border='0'>\n"; $result = dbquery("SELECT * FROM ".DB_GRIMS_BLOG_TOPICS." ORDER BY topic_order ASC"); if (dbrows($result)) { $cnt = 0; while($data = dbarray($result)) { $id = $data['topic_id']; $title = $data['topic_title']; $sub = $data['topic_sub']; $result1 = dbquery("SELECT * FROM ".DB_GRIMS_BLOG_POST." WHERE topic_id='$id'"); $num_rows = dbrows($result1); if ($sub == '1') { echo "<tr><td width='15'></td><td><a class='lnk-side' href='".BASEDIR."grims_blog/topics_page.php?topic_id=".$id."'>$title</a><span style='font-size:11px;color:white;'>&nbsp;[$num_rows posts]</span></td></tr>\n"; } else { echo "<tr><td colspan='2'><a class='lnk-side' href='".BASEDIR."grims_blog/topics_page.php?topic_id=".$id."'>$title</a><span style='font-size:11px;color:white;'>&nbsp;[$num_rows posts]</span></td></tr>\n"; } } $cnt++; } echo "</table><p></div>\n"; ?> The topic_order field is a new field I added to get the desired output but it's not standard procedure and is in fact not really workable in a live setting because I would have to use php_myadmin to modify it everytime I added or deleted a topic or sub-topic. So the bottom line is that I can't figure out anyway to code the script to always show the sub-topic right under the associated main topic and all in order. If I add a sub-topic to one of the upper main topics it shows up at the bottom; hence the addition of the topic_order field. So right now it's basically a mess and I can't figure out how to code everything to work correctly. I have searched the forums here as well as several other sites and cannot get any clues.
  3. Hi All, I have the following, and when i run it the ifnull() is returning null rather than 0 as shown in the attached. Any help on this would be greatly appreciated. select * from ( SELECT ptsl_ptd_id, SUBSTRING(ptsl_date,1,10) as ptsl_date, ptsl_z_id, z_rfid, ptsl_limit FROM `prs_ptsl` inner join prs_z on ptsl_z_id=z_id where ptsl_ptd_id='7' ) as limits left join ( SELECT pr_ptd_id, za_sdate, za_z_id, za_z_rfid, IFNULL(count(za_pr_id), 0) as used FROM `prs_za` inner join prs_pr on za_pr_id=pr_id where prs_pr.pr_status = 'Approved' or prs_pr.pr_status = 'Submitted' group by za_sdate, za_z_id, za_z_rfid ) as used on limits.ptsl_ptd_id=used.pr_ptd_id and limits.ptsl_date=used.za_sdate and ptsl_z_id=za_z_id where ptsl_date = '2021-06-12' and (ptsl_limit - IFNULL(used, 0) >= 0) limit 100
  4. Hello everyone. I'm a self learner that is very new to programming. Three tables are given: table `worker` (worker) with data - id (worker id), first_name (name), last_name (last name) table `child` (child) with data - worker_id (worker id), name (child name) table `car` (machine) with data - worker_id (worker id), model (car model) Table structure: CREATE TABLE `worker` ( `id` int(11) NOT NULL, `first_name` varchar(100) NOT NULL, `last_name` varchar(100) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; CREATE TABLE `car` ( `user_id` int(11) NOT NULL, `model` varchar(100) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; CREATE TABLE `child` ( `user_id` int(11) NOT NULL, `name` varchar(100) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=utf8; It is necessary to write one SQL query that returns: names and surnames of all employees, a list of their children separated by commas and a car brand. You need to select only those workers who have or had a car (if there was a car and then it was gone, then the model field becomes null).
  5. //my controller <?php namespace App\Http\Controllers; use Illuminate\Http\Request; use DB; class homeController extends Controller { public function index() { $employee = DB::table('employee')->orderBy('id','desc')->get(); $department = DB::table('department')->orderBy('id','desc')->get(); return view('index', ['employee' => $employee , 'department' => $department]); } } //my routes Route::get('index','homeController@index'); //my view using blade temmplating engine @foreach($employee as $emp) <div class="employee"> <b>{{ $emp->name }} </b> <a href="employee/{{ $emp->id }}"> <p class="intro">{{ substr($emp->intro ,0, 50) }}...</p> </a> </div> @endforeach @foreach($department as $dep) <div class="department"> <b>{{ $dep->name }} </b> <a href="department/{{ $dep->id }}"> <p class="desc">{{ substr($dep->description ,0, 100) }}...</p> </a> </div> @endforeach I want to fetch using ajax, how can i do it, teach/help me
  6. Hi guys, I'm starting to get back into coding for a hobby and wondering is there a better way of doing these php and mysql calls? they all work but not sure if it was the efficent way or is there others that everyone else uses? //connection for user details lookup $sql0 = "SELECT uid, fullname, emailaddress, created_at FROM MK_users WHERE uid={$_SESSION['id']}"; $result0 = $conn->query($sql0); //connection for user against role acceess $sql2 = "SELECT role_uid, role_bid, role_access, access_created_at FROM MK_role_access WHERE role_uid={$_SESSION['id']}"; $result2 = $conn->query($sql2); while($row2 = $result2->fetch_assoc()) { $_SESSION['role_bid'] = $row2["role_bid"]; } //connection for role access against business lookup $sql3 = "SELECT businessname, account_type, website, main_address, logo FROM MK_baccounts WHERE bid={$_SESSION['role_bid']}"; $result3 = $conn->query($sql3); while($row3 = $result3->fetch_assoc()) { $_SESSION['businessname'] = $row3["businessname"]; }
  7. 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!
  8. I have a table full of mac addresses in this format 80828704B0EE I need a method to run a query that will output this format 80:82:87:04:B0:EE I either need to insert the result of the some query that is formatting the existing mac addresses to another field or use a query to format the mac addresses on the fly. I need to create a csv file from this query. I have all that code it is just making the query to format it the way I need it. Here is a query I found that does the trick except that I get two columns, the original and the formatted one. How can I remove the mac column that this outputs? SELECT mac, concat(left(mac,2), ":",substring(mac,3,2), ":" , substring(mac,5,2), ":",substring(mac,7,2), ":",substring(mac,9,2), ":", right(mac,2)) as mac_formatted FROM phones Thanks
  9. hello dear php-experts, i am currently workin on a litte contact manager. and i want to store the data in a db. Note: i work with Python at the moment. So this is related to Python since i want to dive into Peewee. What is aimed: i am musing on how to insert a list of tuples into the db using Peewee (/note the pyton object-layer-model). My db is setup as follows: class Stats(Model): name = TextField(index=True) gender = TextField() age = TextField() city = TextField() state = TextField() class Meta: database = db My list of tuples looks like this: records = [("Joe Smoe", "Male", 34, "Joe Fracer", " Staten Island")], [("Jane Doe", "Female", 21, "Jane bell", "Capetown")] Well - i wonder how to work out the concrete db-inserts. Should I iterate over the list inserting one row at a time? Can this be bulk inserted or does it need to be made into a dictionary to achieve that solution? i have rows like so (see below). I can do this manually. Of course: rows = [ {"name": "Joe Jackson", "gender": "Male", "age": 44, "city": "Billi Kid ", "state": "Mosqow"}, {"name": "Howard Foster", "gender": "Female", "age": 22, "city": "Jane Austen", "state" :"Capetown"}, ... ] Well - one can do this manually. But honestly: isnt it much better and a lot more efficient to use peewee's Model.insert_many function to do a bulk insert of the data into the database - well with a single SQL INSERT statement? Love to hear from you experts, your dilbert!
  10. hello dear community, i am currently workin on a little python programme that does some extracting from BS4 and storing as list elements in Python. As i am fairly new to Python i need some help with that. Nonetheless, I'm trying to write a very simple Spider for web crawling. Here's my first approach: I need to fetch the data out of this page: http://europa.eu/youth/volunteering/evs-organisation_en Firstly, I do a view on the page source to find HTML elements? view-source:https://europa.eu/youth/volunteering/evs-organisation_en i have to extract data wrapped within multiple HTML tags from the above mentioned webpage using BeautifulSoup4. I have to stored all of the extracted data in a list. But I want each of the extracted data as separate list elements separated by a comma. here we have the HTML content structure: <div class="view-content"> <div class="row is-flex"></span> <div class="col-md-4"></span> <div class </span> <div class= > <h4 Data 1 </span> <div class= Data 2</span> <p class= <i class= <strong>Data 3 </span> </p> <p class= Data 4 </span> <p class= Data 5 </span> <p><strong>Data 6</span> <div class=</span> <a href="Data 7</span> </div> </div> well an approach would be: from urllib.request import urlopen as uReq from bs4 import BeautifulSoup as soup import urllib my_url ='http://europa.eu/youth/volunteering/evs-organisation_en' uClient = uReq(my_url) page_html = uClient.read() uClient.close() page_soup = soup(page_html, "html.parser") cc = page_soup.findAll("td",{"class":""}) for i in range(10): print(cc[0+i].text, i) guess i need some slight changes to code in order to get the thing working.- Code to extract: for data in elem.find_all('span', class_=""): This should give an output: data = [ele.text for ele in soup.find_all('span', {'class':'NormalTextrun'})] print(data) Output: [' Data 1 ', ' Data 2 ', ' Data 3 ' and so forth] question: / i need help with the extraction part... love to hear from you yours dilbert
  11. Hi all, How can i go back to a search results after viewing a link on one of the search result. [search Result] $stmt = $pdo->query(" SELECT * FROM tablename WHERE tablename.position LIKE '%$position%' AND tablename.industry LIKE '%$industry%' "); $stmt->execute(); echo "<table width='100%' class='table-responsive table-hover table-condensed table-striped'>"; echo "<tr> <th bgcolor='#444444' align='center'><font color='#fff'>SN</th> <th bgcolor='#444444' align='center'><font color='#fff'>Firstname</th> <th bgcolor='#444444' align='center'><font color='#fff'>Lastname</th> <th bgcolor='#444444' align='center'><font color='#fff'>Email</th> <th bgcolor='#444444' align='center'><font color='#fff'>Position Applied</th> <th bgcolor='#444444' align='center'><font color='#fff'>Employee Position(Current)</th> <th bgcolor='#444444' align='center'><font color='#fff'>State/Province</th> <th bgcolor='#444444' align='center'><font color='#fff'>Country</th> <th bgcolor='#444444' align='center'><font color='#fff'></th> <th bgcolor='#444444' align='center'><font color='#fff'></th> </tr>"; while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<tr><td>"; echo $i++; echo "</td><td>"; echo $row['firstname']; echo "</td><td>"; echo $row['lastname']; echo "</td><td>"; echo $row['email']; echo "</td><td>"; echo $row['position_applied']; echo "</td><td>"; echo $row['employee_position']; echo "</td><td>"; echo ucwords($row['state']); echo "</td><td>"; echo $row['country']; echo "</td><td>"; echo "<a href='ApplicantProfile?id={$row['email']}'>view more</a>"; echo "</td></tr>"; } echo "</table>"; After viewing ApplicantProfile and i want to go back to the search result, it shows empty without the results? Thanks
  12. Hi all !! I have the following three data tables : mysql> select * from franch; +-----+ | fid | +-----+ | 3 | | 5 | | 7 | +-----+ 3 rows in set (0.00 sec) mysql> select * from master; +-----+-----+ | xid | mid | +-----+-----+ | 5 | 4 | | 7 | 6 | +-----+-----+ 2 rows in set (0.01 sec) mysql> select * from admin; +-----+-----+ | xid | aid | +-----+-----+ | 3 | 2 | | 4 | 2 | +-----+-----+ These are connected on common fields and I was trying to linearize the relationship between them using this query : mysql> SELECT vc.aid, vd.mid as mid, ve.fid as franch FROM franch as ve LEFT JOIN master as vd ON ve.fid = vd.xid LEFT JOIN admin as vc ON vd.mid = vc.xid; which gives the following , almost correct, output. +------------+------------+--------------+ | aid | mid | franch | +------------+------------+--------------+ | NULL | NULL | 3 | | 2 | 4 | 5 | | NULL | 6 | 7 | +------------+------------+--------------+ what I want to achieve as output is this ( difference HIGHLIGHTED in RED ): +---------+---------+---------+ | aid | mid | franch | +---------+---------+---------+ | 2 | NULL | 3 | | 2 | 4 | 5 | | NULL | 6 | 7 | +---------+---------+---------+ In the first since there is no corresponding vale for mid in the master table it produces the null value. Here there has to be a way that when such a null value is produced, the franch should check for a corresponding value in the admin table instead. Thanks all for any help on achieving this !!!
  13. I am trying to capture some data on the fly that is marked to be inserted in the db.. but its a huge dump and is very slow getting it into the db.but i can download the entire package and save it in text in less than 3 minutes.. so the idea is to capture it on the fly and then once off line move the data to the db.. should save hours of time and not tie up the MLS server so long.. So here is what i need.. Want to capture it on the fly which i can do.. but the question is how do i save it to txtfile so that it can easily be reloaded as an arrray in the future. I can break it apart any way i like and add ticks or quotes or anything and save the pieces.. but surely there is a very efficient way to do if for multi array... as i think its important how its stored if say i winat to pull the arrays quickly from file usging something like $str = file('mytextfile.txt'); Hope all this makes sense.. what follows is an example of sample code that i am experimenting with to see how to work this.. any thoughts or suggestions would be appreciated. $contacts = [['name'=>'S Russell','email'=>'s.russell@vahud.com','title'=>'President'], ['name'=>'D Randall','email'=>'d.Randall@vahud.com','title'=>''], ['name'=>'T Joneses','email'=>'t.joneses@vahud.com','title'=>'Professor'], ['name'=>'A Soloing','email'=>'a.soloing@vahud.com']]; Array ( [0] => Array ( [name] => S Russell [email] => s.russell@vahud.com [title] => President ) [1] => Array ( [name] => D Randall [email] => d.Randall@vahud.com [title] => ) [2] => Array ( [name] => T Jones [email] => t.jones@vahud.com [title] => Professor ) [3] => Array ( [name] => A Solo [email] => a.solo@vahud.com ) ) So how should the code be broken apart and placed in saving.. to easily be reloaded quickly exactly like the array it was since it will be fed back to the same program that it was taken from in midstream.
  14. I'm trying to build some data in an excel report through PHP but I'm still having trouble getting the array structure and foreach loops the way I need them. Basically, I'm pulling a select on Mysql, matching it to a select in DB2 ( I need values from each) and then I need to loop the results and perform some calculations and then relate it back to the highest level array for the report. My first query pulls records like this: sku_id | customer_id | locations | group_id | frame | cover | color | start_date 1 5400 2 1 1051 1150 99 2018-02-09 1 5401 3 1 1051 1150 99 2018-02-09 Based on those results I pull a query on DB2 that gets every record since that start_date where those customers ordered that same sku_id so I'm getting multiple records for the sku_id/customercombo. That query returns something like this: customer | frame | cover | color | date | TotalQTY 5400 1051 1150 99 2018-02-09 5 5400 1051 1150 99 2018-02-19 2 5400 1051 1150 99 2018-03-09 5 5401 1051 1150 99 2018-02-11 1 5401 1051 1150 99 2018-02-15 7 5401 1051 1150 99 2018-02-25 5 The queries work and my arrays are printing with the proper data, but I need some guidance on structuring these right. I'm matching the SKU up by attributes like cover, color and frame. I need to look first at each sku_id from the first query, execute the 2nd and then look at each customer to get their total quantity of that sku. So based on the example above I would need to get a total quantity of 12 for customer # 5400 and 13 for customer #5401, both for sku 1. My end goal is to get the total quantity of sku on a per dealer basis, so with the example above, my report would look like this: SKU | GROUP | TotalQTY ----------------------------------------- 1 1 25 My array structure looks right as far as my first foreach (foreach sku, perform the query, which returns an array for each record per customer). I'm not sure how to do the foreach on the customer so that I can work on a per customer basis and get the total quantity of that sku per customer. I feel like I would need to say for each dealerRslt as Customer, total all totalQTY Here's the portion of the script in question: $skuQuery = " SELECT sku_id, customer_id, locations, s.sku_group_id as groupID, s.frame as frame, s.cover1 as cover, s.color1 as color, start_date - interval 7 day as start_date from products p inner join skus s on p.sku_id = s.id where curdate() between p.start_date and p.expire_date group by sku_id, customer_id"; $skuRslt = mysqli_query($conn,$skuQuery); while($skuRow = mysqli_fetch_assoc($skuRslt)){ $skuResult[] = $skuRow; $dealerQuery = " SELECT customer, frame, cover, color, cast(Left(date, 4)||'-'||substring(date,5,2)||'-'||substring(date, 7,2) as date) as start_date, sum(orqtyc) as TotalQTY from table1 where customer = {$skuRow['dealer_id']} AND frame = {$skuRow['frame']} AND color = {$skuRow['color']} AND cover = {$skuRow['cover']} AND date >= " . str_replace('-', '', $skuRow['start_date']) . " group by customer, frame,cover,color,date "; $dealerRslt = odbc_exec($DB2Conn, $dealerQuery); foreach($skuResult as $skuRow){ while($dealerRow = odbc_fetch_array($dealerRslt)){ $dealerResult[] = $dealerRow; print_r($dealerResult); } } }
  15. I'm trying to alter a db2 query in my php script and it's not executing properly when doing so. In the last line of my WHERE clause for the DB2 select, I'm using this: AND date(substr(extd2d,1,4)||'-'||substr(EXTD2d,5,2)||'-'||substr(EXTD2d,7,2)) >= {$row['start_date']} but getting an error that >= is not a valid comparison operand. However, if I remove {$row['start_date']} and use current_date or current_date - 100 DAY, it works perfectly. To clarify, the value for 'start_date' in mysql is DATE data type and the value 'extd2d' in db2 is a packed field value that holds a date (20180202), so I'm converting it to try and match the YYYY-MM-DD format. Why can't I get this to work as it is? $sql = " SELECT sku_id, dealer_id, locations, s.sku_group_id as groupID, s.frame as frame, s.cover1 as cover, s.color1 as color, start_date - interval 7 day as start_date from placements p inner join skus s on p.sku_id = s.id where curdate() between p.start_date and p.expire_date group by sku_id, dealer_id limit 100"; $result = mysqli_query($conn,$sql); while($row = mysqli_fetch_assoc($result)) { $resultData[] = $row; $sql2 = " SELECT framec, covr1c, colr1c, date(substr(extd2d,1,4)||'-'||substr(EXTD2d,5,2)||'-'||substr(EXTD2d,7,2)) as start_date, sum(orqtyc) as TotalQTY from table1 where cstnoc = {$row['dealer_id']} AND framec = {$row['frame']} AND colr1c = {$row['color']} AND covr1c = {$row['cover']} AND date(substr(extd2d,1,4)||'-'||substr(EXTD2d,5,2)||'-'||substr(EXTD2d,7,2)) >= {$row['start_date']} /*This is the line in question*/ group by framec,covr1c,colr1c,extd2d "; $result2 = odbc_exec($DB2Conn, $sql2); while($row2 = odbc_fetch_array($result2)){ $db2Result[] = $row2; } } print_r($resultData); print_r($db2Result);
  16. When I run this I am getting no errors and no data back please can someone tell me what i am doing wrong <?php include 'conn.php'; // Create connection $conn = new mysqli($servername, $username, $password, $dbname); //Query $sql = $conn->prepare("SELECT hotelid, hotelname FROM Hotels WHERE hotelid = '1'"); $sql->execute() ; ?> <div id="container"> <header> <div id="header"> <div class="h1"> <?php while( $row = $sql->fetch()) : ?> <h1><span><?php echo $row['hotelname']; ?></span> <?php endwhile ?>
  17. I'm running the below script using PDO, and I've finally gotten all of the logic working the way I need it to, I believe. However, it seems to be taking forever, mainly with updates. The logic is a bit redundant by nature, due to my constraints with the data structure but I feel like it could still be optimized a bit. The idea is to look at invoices and gather a list of items compared to store fronts for each customer. The logic I have says "If this order for customer A has item X and there already exists a record for Item x with this customer, and the record is 'expired', insert this record. However, if the record exists and we are still within the expiration date, just update. IF no record exists, insert as well". It also does the inserts based on quantity or storefront count, depending on which is greater. This works perfectly well, but now I have the issue of speed and extra redundancy. If Customer A has 10 stores orders 10 of Item X today and it's their first order, I'll insert 10 records, one for each item per storefront. But if they order 10 more in 2 days, and the day after and the day after......etc. The script updates as it should but the way I have it, it's selecting so many records at that point to check for unexpired records to update. Part of the issue is that for every record,I have to run the select query to check for existing records. Otherwise, if I do an initial select and the records in question are back to back, it will just insert thinking that it doesn't exist. There has to be a much cleaner, better and more efficient way to do this but I feel like I'm in over my head $detailStatCheck = " SELECT invnoc as INVOICE, fstatc as STATUS, cstnoc AS DEALER, framec AS FRAME, covr1c AS COVER, colr1c AS COLOR , extd2d AS SHIPDATE, orqtyc AS QUANTITY, case when p.stores is null then 1 else p.stores end as STORES FROM table1 g left join table2 p on g.cstnoc = p.cstno WHERE fstatc in ('S','O') AND date(substr(extd1d,1,4)||'-'||substr(EXTD1d,5,2)||'-'||substr(EXTD1d,7,2) ) between current_Date - 451 DAY AND current_Date order by date(substr(extd1d,1,4)||'-'||substr(EXTD1d,5,2)||'-'||substr(EXTD1d,7,2) ) asc, invnoc asc "; try { $detailCheck = $DB2conn->prepare($detailStatCheck); $detailRslt = $detailCheck->execute(); $count2 = $detailCheck->fetch(); print_r($count2); }catch(PDOException $ex) { echo "QUERY ONE FAILED!: " .$ex->getMessage(); } //Create prepared INSERT statement $insertPlacement = " INSERT ignore INTO dealerplacetest (sku_id, group_id, dealer_id, start_date, expire_date, locations, /*order_num,*/ quantity) SELECT id, sku_group_id, :DEALER, date_add(convert(:SHIPDATE,date), interval 7 day) as start_date, date_add(convert(:SHIPDATE,date), interval 127 day) as expire_date, :STORES, -- :INVOICE, :QUANTITY FROM skus s WHERE s.frame=:FRAME AND s.cover1=:COVER AND s.color1=:COLOR "; //perpare query to check for existing records that are expired $validCheck = " SELECT sku_id, dealer_id, expire_date FROM dealerplacetest p INNER JOIN skus s ON p.sku_id = s.id WHERE p.dealer_id = :DEALER AND s.frame = :FRAME AND s.cover1 = :COVER AND s.color1 = :COLOR AND p.expire_date > date_add(convert(:SHIPDATE,date), interval 7 day) "; $updatePlacement = " UPDATE dealerplacetest d inner join skus s ON d.sku_id = s.id SET expire_date = DATE_ADD(DATE_FORMAT(CONVERT(:SHIPDATE, CHAR(20)), '%Y-%m-%d'),INTERVAL 127 DAY), quantity = :QUANTITY WHERE d.dealer_id = :DEALER AND s.frame = :FRAME AND s.cover1 = :COVER AND s.color1 = :COLOR "; $checkExistingValid = $MysqlConn->prepare($validCheck); $insert = $MysqlConn->prepare($insertPlacement); $update = $MysqlConn->prepare($updatePlacement); while ($row2 = $detailCheck->fetch(PDO::FETCH_ASSOC)) { $values = [ ":DEALER" => $row2["DEALER"], ":SHIPDATE" => $row2["SHIPDATE"], ":STORES" => $row2["STORES"], ":QUANTITY" => $row2["QUANTITY"], ":INVOICE" => $row2["INVOICE"], ":FRAME" => $row2["FRAME"], ":COVER" => $row2["COVER"], ":COLOR" => $row2["COLOR"], ]; $values2 = [ ":DEALER" => $row2["DEALER"], ":FRAME" => $row2["FRAME"], ":COVER" => $row2["COVER"], ":COLOR" => $row2["COLOR"], ":SHIPDATE" => $row2["SHIPDATE"], ]; $values3 = [ ":SHIPDATE" => $row2["SHIPDATE"], ":QUANTITY" => $row2["QUANTITY"], ":DEALER" => $row2["DEALER"], ":FRAME" => $row2["FRAME"], ":COVER" => $row2["COVER"], ":COLOR" => $row2["COLOR"], ]; try{ $existingVldRslt = $checkExistingValid->execute($values2); $count4 = $checkExistingValid->fetch(PDO::FETCH_ASSOC); }catch(PDOException $ex){ echo "QUERY TWO FAILED!!!: " . $ex->getMessage(); } print_r($count4); if(!empty($count4) /*&& empty($count3)*/){ print_r("updating"); for($i=0; $i<$row2['QUANTITY']; $i++){ try{ $updateRslt = $update->execute($values3); }catch(PDOException $ex){ echo "UPDATE_FAILED!!!: " . $ex->getMessage(); } } }else{ print_r("inserting"); if($row2["QUANTITY"] >= $row2["STORES"]){ for($i=0; $i<$row2["STORES"]; $i++){ try{ $insertRslt = $insert->execute($values); }catch(PDOException $ex){ echo "INSERT_FAILED!!!: " . $ex->getMessage(); } } }elseif($row2["QUANTITY"] < $row2["STORES"]){ for($i=0; $i<$row2["QUANTITY"]; $i++){ try{ $insertRslt = $insert->execute($values); }catch(PDOException $ex){ echo "INSERT_FAILED!!!: " . $ex->getMessage(); } } } } }
  18. I'm trying to convert these 2 existing select queries: //existing records that are expired $expiredCheck = " SELECT sku_id, dealer_id, expire_date FROM dealerplacetest p INNER JOIN skus s ON p.sku_id = s.id WHERE p.dealer_id = :DEALER AND s.build = :build AND s.cov = :cov AND s.col = :col AND p.expire_date <= date_add(convert(:ship,date), interval 7 day) "; //existing records that are not expired $validCheck = " SELECT sku_id, dealer_id, expire_date FROM dealerplacetest p INNER JOIN skus s ON p.sku_id = s.id WHERE p.dealer_id = :DEALER AND s.build = :build AND s.cov = :cov AND s.col = :col AND p.expire_date > date_add(convert(:ship,date), interval 7 day) "; $checkExisting = $MysqlConn->prepare($expiredCheck); $checkExistingValid = $MysqlConn->prepare($validCheck); $existingRslt = $checkExisting->execute($values2); $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC); $existingVldRslt = $checkExistingValid->execute($values2); $count4 = $checkExistingValid->fetch(PDO::FETCH_ASSOC); Into one general select like so: //select records alltogether, check for expiration later in loop $expiredCheck = " SELECT sku_id, dealer_id, expire_date FROM dealerplacetest p INNER JOIN skus s ON p.sku_id = s.id WHERE p.dealer_id = :DEALER AND s.build = :build AND s.cov = :cov AND s.col = :col "; $checkExisting = $MysqlConn->prepare($expiredCheck); And then use my existing bound parameters ($values2) to formulate an if statement that will replace the previously existing expiration date check. Basically I've taken the 2 previous expiration date checks from the query and plugged them into the if statement but it fails the '7' in my day interval. I feel like there may be a different syntax standard to use here but I can't seem to find the proper solution. I'm trying to create a $count3 variable for the first check and a $count4 variable for the 2nd check. Here are params and if statement: $values2 = [ ":DEALER" => $row2["DEALER"], ":build" => $row2["build"], ":cov" => $row2["cov"], ":col" => $row2["col"], ":ship" => $row2["ship"], ]; $existingRslt = $checkExisting->execute($values2); while($existingRow = $checkExisting->fetch(PDO::FETCH_ASSOC)){ if($existingRow["expire_date"] <= date_add(convert(":ship",date), interval 7 day){ $count3 = $checkExisting->fetch(PDO::FETCH_ASSOC); }elseif($existingRow["expire_date"] > date_add(convert(":ship",date), interval 7 day)){ $count4 = $checkExisting->fetch(PDO::FETCH_ASSOC); } } I'm just trying to consolidate SELECT queries if possible. What am I doing wrong?
  19. Here's a scenario. I am setting up a sponsor referral link. If any user signs up on the site through that referral link, they will be matched with the sponsor of that referral link. Normally I can do this using a simple GET method but I want to use cookies so that the referral link will be valid for 30 days. So if a user decides to come back to the site a week later, they will still be matched with that sponsor assuming they haven't deleted their cookies. For eg. // referral link mysite.com/signup?sponsor=john Here is my cookie code. The issue i am having is that if I go to a different page on the site and come back to the signup page, the cookie gets reset or becomes invalid. Can you tell me what i'm doing wrong? Do I have to use the database to store the cookies or what? $get_user = $_GET['sponsor']; $number_of_days = 30 ; $date_of_expiry = time() + 60 * 60 * 24 * $number_of_days ; setcookie( "sponsor", $get_user, $date_of_expiry); if(isset($_COOKIE['sponsor'])) { echo 'set'; } else { echo 'not set'; }
  20. hi, i recently changed my host from openshift to heroku and from then on my problems started to grow. my main conn from dbconfig file is nothing but my login script is able to fetch results yet not able to login tried heroku support it didn't work. So, i decided to try my luck with you php experts to guide me create a connection and make login process easy. here is my db config file: $url=parse_url(getenv("CLEARDB_DATABASE_URL")); $server = $url["host"]; $username = $url["user"]; $password = $url["pass"]; $db = substr($url["path"],1); $host= "mysql:host=$server;dbname=$db"; try { $conn=new PDO("mysql:host=$server;dbname=$db" , $username, $password); $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $exc) { echo $exc->getMessage(); } //var_dump($url); var_dump($conn); echo "<br>"; var_dump($exc); include 'classes.inc.php'; //echo "<br>".$db."<br>"; $project= new projecteg($conn); var_dump($project); here is my login script: include_once 'dbconfig.inc.php'; if (isset($_POST['submit-login'])) { $uname= htmlspecialchars($_POST['unamel']); $unamel= stripslashes($_POST['unamel']); $pass= htmlspecialchars($_POST['passl']); $pass1= stripslashes($_POST['passl']); $passl= md5($pass1); $user = $project->viewProtectedArea($unamel,$passl); var_dump($user); exit(); if ($user!="") { some conditions ... } here is my output: object(PDO)#1 (0) { } NULL object(projecteg)#2 (4) { ["_db":"projecteg":private]=> object(PDO)#1 (0) { } ["query"]=> NULL ["stmth"]=> NULL ["conn"]=> NULL } array(1) { [0]=> array(21) { ["user_id"]=> string(3) "142" ["fname"]=> string(6) "gowri " ["lname"]=> string(7) "shanker" ["uname"]=> string(15) "aboutthecreator" ["pass"]=> string(32) "0c484476449dfd9a8bdf826bee31f03c" ["email"]=> string(21) "gsshanker10@gmail.com" ["phone"]=> string(1) "0" ["avatar"]=> string(25) "TueDec619064920166367.jpg" ["activated"]=> string(1) "1" ["notescheck"]=> string(19) "2017-08-14 14:51:53" ["work"]=> string(22) "inventor, entreprenuer" ["graduation"]=> string(39) "Maharaja arts and science college, cbe." ["school"]=> string(43) "Seventh day adventist, chockikulam, madurai" ["city"]=> string(14) "chennai, india" ["about_me"]=> string(123) "To tell about myself i'm a cool headed guy who loves to take risks and experiment with things if i have something in excess" ["residence"]=> string(18) "I reside in India." ["gender"]=> string(4) "male" ["interests"]=> string(116) "I love Bikes, love to chat, and spend some time on thinking on action plans about problems i face and also in women." ["quote"]=> string(79) "write something worth reading or do something worth reading- Benjamin Franklin." ["privacy"]=> string(1) "0" ["like_person_count"]=> string(1) "1" } }
  21. Hi every one, I have play with this code for a whole day and still can not get it working. Any helps will be appreciated and will give donation. Thanks in advance <?Php $connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa'); if ($connection->connect_errno > 0) { die ('Unable to connect to database [' . $connection->connect_error . ']'); } $query1 = "SELECT SUM(sale + tip) as totals FROM everyday_sale where DATE(s.reg_date) = CURDATE()"; $result1 = mysql_query($query1); if (!$result1) { $message = 'Invalid query: ' . mysql_error() . "\n"; $message .= 'Whole query: ' . $query1; die($message); } while ($row1 = mysql_fetch_assoc($result1)) { echo $row1['totals']; } mysql_free_result($result); ?> And below is the code of the whole php file <HTML> <HEAD> <TITLE></TITLE> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script src="onpage_submit.js"></script> <META name="description" content=""> <META name="keywords" content=""> <META name="generator" content="CuteHTML"> </HEAD> <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080"> <table width="800" align="center"><tr><td align="center">hahahha</td></tr><tr> <?Php $connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa'); if ($connection->connect_errno > 0) { die ('Unable to connect to database [' . $connection->connect_error . ']'); } $sql = "SELECT t.id, t.firstname, SUM(s.sale) as sales, SUM(s.tip) as tips, SUM(s.sale + s.tip) as totals FROM technictians t LEFT JOIN everyday_sale s ON s.technictian_id = t.id where DATE(s.reg_date) = CURDATE() GROUP BY t.id"; if (!$result = $connection->query($sql)) { die ('There was an error running query[' . $connection->error . ']'); } ?> <?php $rows = $result->num_rows; // Find total rows returned by database if($rows > 0) { $cols = 3; // Define number of columns $counter = 1; // Counter used to identify if we need to start or end a row $nbsp = $cols - ($rows % $cols); // Calculate the number of blank columns echo '<table border="1" bgcolor="#E0F2F7" bordercolor="blue" width ="800" height ="700" align="center">'; while ($row = $result->fetch_array()) { if(($counter % $cols) == 1) { // Check if it's new row echo '<tr>'; } echo '<td><table align="center"><tr><td align="center"><font size="5" color="red"><b>'.$row['firstname'].'</b></font></td></tr><tr><td><hr></td></tr> <tr><td align="center"><font sie="5" color="maroon"><b>Earning Totals:</b></font></td></tr> <tr><td align="center"><font size="4" color="green"><b>Sales: $ '.$row['sales'].'</b></font></td></tr> <tr><td align="center"><font size="4" color="green"><b>Tips: $ '.$row['tips'].'</b></font><hr></td></tr> <tr><td align="center"><font size="4" color="red"><b>Total: $ '.$row['totals'].'</b></font></td></tr> <tr><td><hr></td></tr> </table></td>'; if(($counter % $cols) == 0) { // If it's last column in each row then counter remainder will be zero echo '</tr>'; } $counter++; // Increase the counter } $result->free(); if($nbsp > 0) { // Add unused column in last row for ($i = 0; $i < $nbsp; $i++) { echo '<td> <?Php $connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa'); if ($connection->connect_errno > 0) { die ('Unable to connect to database [' . $connection->connect_error . ']'); } $query1 = "SELECT SUM(sale + tip) as totals FROM everyday_sale where DATE(s.reg_date) = CURDATE()"; $result1 = mysql_query($query1); if (!$result1) { $message = 'Invalid query: ' . mysql_error() . "\n"; $message .= 'Whole query: ' . $query1; die($message); } while ($row1 = mysql_fetch_assoc($result1)) { echo $row1['totals']; } mysql_free_result($result); ?> </td>'; } echo '</tr>'; } echo '</table>'; } ?> </td></tr></table> </BODY> </HTML>
  22. Hi, i'm new to php and mysql so any helps will be greatly appreciated. I have the the code below but it looks like it's not working. I need the data id, firstname from table technictians and the total sales(add all sales in the database together) in the everyday_sale table. It's give me the total sale in the second query but not the id and firstname from the first query. If i take out the second query then the first query gave me the id and firstname just fine. It's seem like that two queries dont like each other some how. Thanks in advance and will consider give donation if got the problem solve. <HTML> <HEAD> <TITLE></TITLE> <script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.0/jquery.min.js"></script> <script src="onpage_submit.js"></script> <META name="description" content=""> <META name="keywords" content=""> <META name="generator" content="CuteHTML"> </HEAD> <BODY BGCOLOR="#FFFFFF" TEXT="#000000" LINK="#0000FF" VLINK="#800080"> <?Php $connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa'); if ($connection->connect_errno > 0) { die ('Unable to connect to database [' . $connection->connect_error . ']'); } $sql = "SELECT * FROM technictians"; if (!$result = $connection->query($sql)) { die ('There was an error running query[' . $connection->error . ']'); } ?> <?Php $connection = new mysqli('localhost', 'root', 'Mylovev1@', 'usnailsandspa'); if ($connection->connect_errno > 0) { die ('Unable to connect to database [' . $connection->connect_error . ']'); } $query = "SELECT SUM(sale) FROM everyday_sale where technictian_id= '.$row['id'].'"; if (!$result = $connection->query($query)) { die ('There was an error running query[' . $connection->error . ']'); } ?> <?php $rows = $result->num_rows; // Find total rows returned by database if($rows > 0) { $cols = 3; // Define number of columns $counter = 1; // Counter used to identify if we need to start or end a row $nbsp = $cols - ($rows % $cols); // Calculate the number of blank columns echo '<table border="1" bgcolor="#E0F2F7" bordercolor="blue" width ="700" height ="700" align="center">'; while ($row = $result->fetch_array()) { if(($counter % $cols) == 1) { // Check if it's new row echo '<tr>'; } echo '<td><table align="center"><tr><td align="center"><font size="5" color="red"><b>'.$row['firstname'].'</b></font></td></tr><tr><td valign="top"><form action="salaries_add.php" method="post"> Sale:&nbsp&nbsp&nbsp&nbsp&nbsp <input type="text" id="num1" name="sale" style="width: 100px;" /></td></tr><tr><td valign="top"> Tip:&nbsp&nbsp&nbsp&nbsp&nbsp&nbsp <input type="text" id="num2" name="tip" style="width: 100px;" /></td></tr><tr><td valign="top"> Ticket#:<input type="text" id="ticket_number" name="ticket_number" style="width: 100px;" /></td></tr><tr><td valign="top" align="center"> <input type="hidden" name="technictian_id" value="' .$row['id']. '"> <input type="submit" value="Submit"></form></td></tr> <tr><td><hr></td></tr> <tr><td align="center"><font sie="5" color="maroon"><b>Earning Totals:</b></font></td></tr> <tr><td>'.$row['SUM(sale)'].'</td></tr> </table></td>'; if(($counter % $cols) == 0) { // If it's last column in each row then counter remainder will be zero echo '</tr>'; } $counter++; // Increase the counter } $result->free(); if($nbsp > 0) { // Add unused column in last row for ($i = 0; $i < $nbsp; $i++) { echo '<td> </td>'; } echo '</tr>'; } echo '</table>'; } ?> <script> </BODY> </HTML>
  23. I'm trying to create a spreadsheet within a php script that selects data from a mysql query. The query works and my below script works in the sense that it inserts my entire result set into my spreadsheet like so: https://i.stack.imgur.com/Euynu.png The only thing I can't figure out is how to modify the array loop so that I can sum totals of the 4 columns (C,D,E and F) by user. Essentially, it needs to realize that it's at the last record for user ID 48 so I can insert a row of totals, and move to the next user. I feel it should be fairly straightforward but I just can't seem to make sense of it right now. The only other factor is that when this query runs, every user could have up to 5 records so I can use the USER ID to associate a user but the records would hinge on the ID as well as the Date, if that makes sense. How can I get this to group by user and total their numbers before writing the info for the next user? $sql = " select concat(u.first_name, ' ', u.last_name) as Name, t.ext_id as ID, t.total_talk_time_minutes as TalkTime, t.total_outbound as Outbound, t.total_inbound as Inbound, t.dealers_contacted as Dealers, t.date_of_report as Date From ambition.ambition_totals t INNER JOIN ambition.ambition_users u ON t.extension = u.extension where date_of_report between curdate() - interval 5 day and curdate()" or die(mysqli_error( $conn)); $result=mysqli_query($conn,$sql); $spreadsheet = new Spreadsheet(); $sheet = $spreadsheet->getActiveSheet(); $sheet->setCellValue('A1', 'Name'); $sheet->setCellValue('B1', 'User ID'); $sheet->setCellValue('C1', 'Talk Time Per Call'); $sheet->setCellValue('D1', 'Outbound Calls'); $sheet->setCellValue('E1', 'Inbound Calls'); $sheet->setCellValue('F1', 'Dealer Contacts'); $sheet->setCellValue('G1', 'Date'); $rowNumber = 2; while($row = mysqli_fetch_assoc($result)) { $col = 'A'; foreach($row as $cell){ $sheet -> setCellValue($col.$rowNumber, $cell); $col++; } $rowNumber++; }
  24. Hello guys, I'm try to sum rows in a UNION but having a hard time about it $stmt = $pdo->prepare("SELECT due_date, SUM(amount_paid) FROM ( SELECT due_date, amount_paid FROM table1 union all SELECT due_date, amount_paid FROM table2 UNION ALL )x GROUP BY MONTH"); $stmt->execute(); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $row['x']; thanks
  25. select player_id,Gender,Shirt_no FROM player_details; +-----------+--------+----------+ | player_id | Gender | Shirt_no | +-----------+--------+----------+ | 10 | M | 34 | | 11 | M | 12 | | 12 | M | 13 | | 13 | M | 34 | +-----------+--------+----------+ 13 rows in set (0.00 sec) select player_id,Team_catId from players_team; +-----------+------------+ | player_id | Team_catId | +-----------+------------+ | 10 | 1 | | 12 | 2 | | 11 | 3 | | 13 | 1 | +-----------+------------+ i would like to put a check constraint on the shirt_no field,such that no player in same team category has same shirt number i have tried this: CREATE TABLE player_details ( player_id INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, Fname VARCHAR(60) NOT NULL, Gender ENUM('M','F'), Shirt_no tinyint(2), PRIMARY KEY (player_id ), CONSTRAINT Shirt_number_taken CHECK (NOT EXISTS -- reference to second table (SELECT * FROM Soka_players_team_tbl AS M1 WHERE M1.player_id = Soka_player_details_tbl.player_id )) ); but it is not working.
×
×
  • 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.